How to return a query result from postgresql function

Question

I am new to Postgres, in ms sql server we can write stored procedure to perform logic and return a select statement. Likewise i wrote following pqsql function with return type table. But its showing some incorrect syntax error, but if I replace the return type with integer and comment out the select statement it works fine.

following is the function with return type table

CREATE OR REPLACE FUNCTION candidate_save
(
    p_name varchar(50),
    p_dob date,
    p_course_level_code integer,
    p_email varchar(50),
    p_mob character(10),
    p_sslc_regno varchar(10),
    p_sslc_year_of_passing character(4),
    p_sslc_board_of_examination  integer,
    p_password character(128),
    p_ip_address varchar(15)
)
RETURNS TABLE 
(
    regno char(10),
    name varchar(50),
    dob date,
    course_level_code integer,
    email varchar(50),
    mob character(10),
    sslc_regno varchar(10),
    sslc_year_of_passing character(4),
    sslc_boe  integer
)
AS 
$BODY$
DECLARE
    max_row_count integer;
    random_no integer;
    v_regno character(10);
BEGIN

    SELECT COUNT(*) INTO max_row_count FROM candidates WHERE course_level_code = p_course_level_code;

    max_row_count := max_row_count + 1;

    v_regno := cast(p_course_level_code as character) || cast( trunc(random() * 89999 + 10000) as integer) || to_char(max_row_count, 'FM0000');

    INSERT INTO candidates 
        (regno, name, dob, course_level_code, email, mob, sslc_regno, sslc_year_of_passing, sslc_board_of_examination, password, created_on, ip_address)
    VALUES 
        (v_regno, p_name, p_dob, p_course_level_code, p_email, p_mob, p_sslc_regno, p_sslc_year_of_passing, p_sslc_board_of_examination, p_password, now(), p_ip_address);

    RETURN QUERY
        SELECT regno, name, dob, course_level_name, email, mob, sslc_regno, sslc_year_of_passing, c.board as sslc_boe 
        FROM candidates a 
            INNER JOIN course_levels b on a.course_level_code = b.course_level_code
            INNER JOIN sslc_board_of_examinations c ON a.sslc_board_of_examination = boe_code
        WHERE regno = v_regno;

END;
$BODY$
LANGUAGE plpgsql;

when creating above function following error is showing

ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO candidates ( $1 ,  $2 ,  $3 ,  $4 ,  $5 ,  $6 , ...
                                 ^
QUERY:  INSERT INTO candidates ( $1 ,  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 ,  $8 , sslc_board_of_examination, password, created_on, ip_address) VALUES ( $9 ,  $10 ,  $11 ,  $12 ,  $13 ,  $14 ,  $15 ,  $16 ,  $17 ,  $18 , now(),  $19 )
CONTEXT:  SQL statement in PL/PgSQL function "candidate_save" near line 30

********** Error **********

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "candidate_save" near line 30

but if I replace the table with integer it works fine.

CREATE OR REPLACE FUNCTION candidate_save
(
    p_name varchar(50),
    p_dob date,
    p_course_level_code integer,
    p_email varchar(50),
    p_mob character(10),
    p_sslc_regno varchar(10),
    p_sslc_year_of_passing character(4),
    p_sslc_board_of_examination  integer,
    p_password character(128),
    p_ip_address varchar(15)
)
RETURNS integer
AS 
$BODY$
DECLARE
    max_row_count integer;
    random_no integer;
    v_regno character(10);
BEGIN

    SELECT COUNT(*) INTO max_row_count FROM candidates WHERE course_level_code = p_course_level_code;

    max_row_count := max_row_count + 1;

    v_regno := cast(p_course_level_code as character) || cast( trunc(random() * 89999 + 10000) as integer) || to_char(max_row_count, 'FM0000');

    INSERT INTO candidates 
        (regno, name, dob, course_level_code, email, mob, sslc_regno, sslc_year_of_passing, sslc_board_of_examination, password, created_on, ip_address)
    VALUES 
        (v_regno, p_name, p_dob, p_course_level_code, p_email, p_mob, p_sslc_regno, p_sslc_year_of_passing, p_sslc_board_of_examination, p_password, now(), p_ip_address);

    RETURN 1;

END;
$BODY$
LANGUAGE plpgsql;

if I am doing something wrong pl. let me know.

I am using Postgres 9.4

Answer 1

finally it started working after I changed the variable names in the return table. Thanks to all

Answer 2

INSERT INTO candidates 
    (regno
     ^------- The error

ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO candidates ( $1

The issue is that regno is a plpgsql variable, because it's a column name in the output table.

RETURNS TABLE 
(
    regno char(10),

Each entry in RETURNS TABLE is registered as a variable so you can assign to it and use RETURN NEXT.

When parsed and processed, plpgsql variables in statements are replaced with positional parameters like $1, $2, etc. Which is why the error is what it is, and also why some of the later entries in the insert column-name-list don't get replaced. They don't clash with a parameter name.

Use different variable names.

I think 9.5 detects parameter name clashes and reports a clearer error.