0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eunito
  • 416
  • 5
  • 22

1 Answers1

2

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.

  • would it be possible to return 2 or more selects from diferent tables based on the p_id parameter? For example: if p_id = 1 returns the select from schemaZ.mainTable, if p_id = 2 then it returns the select from schemaz.tableSecondary1, etc ? – Eunito Apr 17 '20 at 19:52