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
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.
'' 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.
SELECT version()produces the server version, whilepsql --versionprints the version of thepsqlclient. 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 whyselect 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.