Search code examples
postgresqlstored-functions

Create view using postgres function

I'm trying to build a parametrized view using a postgres function:

CREATE FUNCTION schemaB.testFunc(p INT)
RETURNS TABLE 
AS
RETURN (SELECT * FROM schemaZ.mainTable WHERE id=p)

The problem is always the same:

SQL Error [42601]: ERROR: syntax error at or near "AS"

Any idea on what could I be doing wrong?

Solution

  • You need to specify the columns of the "return table", this is either done using

    returns table(col_1 integer, col_2 text, ...)

    In your case you are returning only rows of one table, so it's easier to use

    returns setof maintable

    As documented in the manual the function body needs to be enclosed in single quotes, or using dollar quoting.

    As stored functions can be written in many different languages in Postgres, you also need to specify a language - in this case language sql is suitable.

    So putting all that together, you need:

    CREATE FUNCTION schemaB.testFunc(p_id INT)
  RETURNS setof  schemaZ.mainTable
AS
$$
 SELECT * 
 FROM schemaZ.mainTable 
 WHERE id = p_id
$$
language sql;

    A return statement is not required for language sql functions.