SQL function "RETURNS TABLE" syntax error in PostgreSQL 8.1

Question

In a user defined function how do you return a table formed from the projection of joining other tables?

Here is a simple example

CREATE FUNCTION something123(character varying(100)) RETURNS TABLE (a integer, b character varying(300)) AS
$$
  SELECT b.year, p.materialUsed FROM bestBefore b join packaged p on b.id=p.id WHERE id=$1;
$$
LANGUAGE SQL
;

It always errors at TABLE. How would you get the contents of that select statement to return?

I'm using Postgres 8.1.21

Answer updated to reflect the version. Especially when using a truly ancient version of PostgreSQL like 8.1 (released November 2005, final update November 2010) please mention your version clearly in all questions. — Craig Ringer, Oct 24 '12 at 08:40
@CraigRinger should I be using select version(); in Postgres or psql --version in the terminal? Why are they different anyways? — Celeritas, Oct 24 '12 at 08:48
They most certainly are different. On most systems they'll produce the same result, but `SELECT version()` produces the *server* version, while `psql --version` prints the version of the `psql` client. It's entirely possible to use (say) psql 9.2 to connect to PostgreSQL 8.4, so the versions aren't necessarily the same. For most questions it's the server version you care about, which is why `select version()` is preferred. Some systems (commonly Mac OS X) frequently land up with multiple PostgreSQL versions on them, so knowing it's the server version can matter. — Craig Ringer, Oct 24 '12 at 08:53
@a_horse_with_no_name It's just for a class so I guess no one would bother hacking into it. — Celeritas, Oct 24 '12 at 20:31
8.1 lacks many modern SQL features. *Especially* when learning, you should use an up-to-date version. You wouldn't teach someone to driver with a Ford Model-T would you? — , Oct 24 '12 at 20:33
@a_horse_with_no_name I agree it's a load of crap. What's your mailing address? I'll tell the prof to forward the paycheck to you. — Celeritas, Oct 24 '12 at 20:38

Craig Ringer · Accepted Answer · 2012-10-24T08:38:59.900

Your code is fine, but your PostgreSQL version isn't. It doesn't support RETURNS TABLE, as per the PostgreSQL 8.1 documentation for CREATE FUNCTION.

On extremely old PostgreSQL versions like 8.1 you must declare RETURNS SETOF RECORD without using RETURNS TABLE, as it wasn't supported by that old version. RETURNS SETOF RECORD causes the function to return an anonymous record set. You must then specify the record structure at the call site, like this:

regress=# CREATE FUNCTION something123_legacy(character varying(100))
RETURNS SETOF RECORD AS
$$
  SELECT 1, 'fred'::varchar(300);
$$
LANGUAGE SQL;

regress=# SELECT * FROM something123_legacy('blah') somethingresult(col1name integer, col2name character varying(300));
 col1name | col2name 
----------+----------
        1 | fred
(1 row)

Alternately, you can CREATE TYPE to create a defined rowtype, or use an existing table type since every table has a rowtype of the same name. Your function can then return that rowtype.

regress=# CREATE TYPE something123type AS (col1name integer, col2name character varying(300));
CREATE TYPE
regress=# CREATE FUNCTION something123_legacy2(character varying(100))
    RETURNS SETOF something123type AS
    $$
      SELECT 1, 'fred'::varchar(300);
    $$
    LANGUAGE SQL;
CREATE FUNCTION
regress=# SELECT * FROM something123_legacy2('blah');
 col1name | col2name 
----------+----------
        1 | fred
(1 row)

You could also try using OUT parameters, but I seem to vaguely remember that they were only supported for PL/PgSQL (not SQL functions) at one point and I'm not sure they work in 8.1. Try it:

CREATE FUNCTION something123( IN character varying(100), OUT integer, OUT character varying(300) ) RETURNS setof record AS
$$
  SELECT b.year, p.materialUsed FROM bestBefore b join packaged p on b.id=p.id WHERE id=$1;
$$
LANGUAGE SQL;

WARNING: Your PostgreSQL version is unsupported and has been for two years. It is not getting security or bug fixes. Eventually you're going to have to upgrade, and the longer you wait the harder it's going to get. Start planning your upgrade now. Read the release notes of every .0 version (8.2.0, 8.3.0, etc) between yours and the current version, paying particular attention to the upgrade notes and compatibility notes. Watch out for the removal of implicit casts to text, bytea_output change, and standard_conforming strings change. Read the upgrade section of the manual for the new version and take note of advice like using the new version's pg_dump.

Ok thanks I got it working. The last part I had a problem with was I wanted to compare the input so I went '$1' but I needed to drop the ' '(otherwise 0 rows get returned). So if the argument is of type character varying you don't need the single quotes when referencing it? — Celeritas, Oct 24 '12 at 08:46
@Celeritas ... I'm not sure what you mean there. `''` is differnt to `'$1'` is different to `$1` if that's what you're asking; the first is the empty string, the second is the string `'$1'` and the other expands to a parameter. But you didn't write `''` in your question and I didn't in the answer, so I'm not sure what you're getting at. Are you asking about my use of a trivial dummy statement in the above instead of your SELECT statement? 'cos that was just for convenient testing. — Craig Ringer, Oct 24 '12 at 08:51

SQL function "RETURNS TABLE" syntax error in PostgreSQL 8.1

1 Answers1