User Defined Functions returning Tables in PostgreSQL

Question

I'm trying to create a UDF which returns a table containing four values:

CREATE TABLE ts_table
(
  id character varying, 
  year integer, 
  rank double precision, 
  votes integer
);

Then, using a UDF, I have to return a table defined by (something along the lines of):

CREATE FUNCTION ts_myfunc(text) RETURNS SETOF ts_table AS $$
    'SELECT id,year,rank,votes FROM productions NATURAL JOIN directors NATURAL JOIN ratings WHERE attr is NULL AND pid=$1 ORDER BY year;'
$$ LANGUAGE SQL;

However, when I try to create the UDF with that command, I get

ERROR:  syntax error at or near "'SELECT id,year,rank,votes FROM productions NATURAL JOIN directors NATURAL JOIN ratings WHERE attr is NULL AND pid=$1 ORDER BY year;'"
LINE 2: 'SELECT id,year,rank,votes FROM productions NATURAL JOIN dir...

Read up on ["dollar-quoting"](http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING). This closely related question may be of help: http://stackoverflow.com/questions/12144284/what-are-used-for-in-pl-pgsql/12172353#12172353 — Erwin Brandstetter, Feb 16 '14 at 03:25
@ErwinBrandstetter thanks, I noticed my mistake a little too late but I figured I'd leave it as an answer to future, similar questions. — Tyler Sebastian, Feb 16 '14 at 09:06

score 0 · Answer 1 · answered Feb 16 '14 at 00:32

Changing the line 'SELECT id,year,rank,votes FROM productions NATURAL JOIN directors NATURAL JOIN ratings WHERE attr is NULL AND pid=$1 ORDER BY year;'

to

SELECT id,year,rank,votes FROM productions NATURAL JOIN directors NATURAL JOIN ratings WHERE attr is NULL AND pid=$1 ORDER BY year;

(remove the single quotes) will solve the problem.

User Defined Functions returning Tables in PostgreSQL

1 Answers1