Syntax Error creating function in SQL for Postgres 9 database

Question

I am trying to create the following function in a postgres_9 database:

CREATE OR REPLACE FUNCTION getUser(IN user_id INT8)
RETURNS TABLE(id INT8, nom TEXT, prenom TEXT)
AS $$
    BEGIN
        SELECT id, nom, prenom 
        FROM security.users
        WHERE id = user_id
    END
$$ LANGUAGE sql;

But I get the following error:

ERROR: syntax error at or near "SELECT"

PS: I am new to Postgres databases. if you have good resources for tutorials and books on how to create functions and triggers in plpgsql or SQL for Postgres databases that would be great!

Answer 1

Can you try with this approach as suggested in this Postgresql 9.1 Create Function

CREATE FUNCTION getUser(int)
RETURNS TABLE(id int, nom text, prenom text)
AS $$ SELECT id, nom, prenom 
      FROM security.users
      WHERE id = $1 $$
LANGUAGE SQL;

Answer 2

PostgreSQL has two default languages for stored procedures (function): PLpgSQL and SQL. SQL is raw SQL. PLpgSQL is ADA based procedural language with block structure - block is defined by keywords BEGIN, END. You can write your function in both these languages. You should to specify language correctly.

CREATE OR REPLACE FUNCTION getUser(IN user_id INT8)
RETURNS TABLE(id INT8, nom TEXT, prenom TEXT)
AS $$
BEGIN
  RETURN QUERY SELECT id, nom, prenom 
                 FROM security.users
                 WHERE id = user_id;
END
$$ LANGUAGE plpgsql;

or

CREATE OR REPLACE FUNCTION getUser(IN user_id INT8)
RETURNS TABLE(id INT8, nom TEXT, prenom TEXT)
AS $$
   SELECT id, nom, prenom 
     FROM security.users
    WHERE id = $1 
$$ LANGUAGE sql;

SQL language in 9.1 doesn't support named parameters, so you should to use $1, $2, ... for parameters.