How to define variable in function with Postgres

Question

I am new to Postgres and this issue has cost me hours of frustration. I am sure I have the syntax correct and all reference material and other posts indicate to me anyway that it is OK. Please, can anyone help me!

CREATE OR REPLACE FUNCTION "SomeSchema".insertDataAccessType(IN type_name char varying, IN created_by char varying)
  RETURNS SETOF "SomeSchema"."DataAccessTypes" LANGUAGE 'sql' AS
    $$
    DECLARE newId INTEGER;
        BEGIN
            INSERT INTO "SomeSchema"."DataAccessTypes"(type_name, created_by)
            VALUES (insertDataAccessType.type_name, insertDataAccessType.created_by)
            RETURNING id INTO newId;

            RETURN QUERY SELECT * FROM "SomeSchema".getdataaccesstypebyid(newId);
        END;
    $$;

I am just trying to create this function that will insert a record, add the newly created ID into the variable I am declaring and the do a select on the new ID to return a result set. All I am getting though is this damn error message!

ERROR:  syntax error at or near "INTEGER"
LINE 4:  DECLARE newId INTEGER;
                       ^
SQL state: 42601
Character: 215

I am used to MSSQL which is so much damn easier to do these simple things with. I am sure the issue is something simple I am missing being new to the tech but can someone please point me in the right direction as to how to correct this as it is driving me nuts.

Thanks,

score 0 · Answer 1 · edited Apr 26 '19 at 04:37

Well after reading even more looks like I have solved my issue. When I want to do an Insert and and Select in the same function I needed to alter my language. So I changed this line:

FROM

RETURNS SETOF "SomeSchema"."DataAccessTypes" LANGUAGE 'sql' AS

TO

RETURNS SETOF "SomeSchema"."DataAccessTypes" LANGUAGE plpgsql AS

and the function created fine and executed perfectly. Hopefully, this will help some other Postgres newbies. More reading here Difference between language sql and language plpgsql in PostgreSQL functions and here: https://www.postgresql.org/docs/current/plpgsql-overview.html#PLPGSQL-ADVANTAGES really helped

How to define variable in function with Postgres

1 Answers1