Create function with temporary tables that return a select query using these temp tables

The most probable error this could raise in Postgres:

ERROR: column "foo" specified more than once

Meaning, there is at least one more column name (other than id which is folded to one instance with the USING clause) included in both tables. This would not raise an exception in a plain SQL SELECT which tolerates duplicate output column names. But you cannot create a table with duplicate names.

The problem also applies for Greenplum (like you later declared), which is not Postgres. It was forked from PostgreSQL in 2005 and developed separately. The current Postgres manual hardly applies at all any more. Look to the Greenplum documentation.

And psql is just the standard PostgreSQL interactive terminal program. Obviously you are using the one shipped with PostgreSQL 8.2.15, but the RDBMS is still Greenplum, not Postgres.

Syntax fix (for Postgres, like you first tagged, still relevant):

CREATE OR REPLACE FUNCTION myfunction() RETURNS TABLE (column_a text, column_b text) AS $func$ BEGIN CREATE TEMPORARY TABLE raw_data ON COMMIT DROP AS SELECT d.column_a, d2.column_b -- explicit SELECT list avoids duplicate column names FROM dummy_data d JOIN dummy_data_2 d2 using (id); RETURN QUERY SELECT DISTINCT column_a, column_b FROM raw_data LIMIT 100; END $func$ LANGUAGE plpgsql SECURITY DEFINER; 

The example wouldn't need a temp table - unless you access the temp table after the function call in the same transaction (ON COMMIT DROP). Else, a plain SQL function is better in every way. Syntax for Postgres and Greenplum:

CREATE OR REPLACE FUNCTION myfunction(OUT column_a text, OUT column_b text) RETURNS SETOF record AS $func$ SELECT DISTINCT d.column_a, d2.column_b FROM dummy_data d JOIN dummy_data_2 d2 using (id) LIMIT 100; $func$ LANGUAGE plpgsql SECURITY DEFINER; 

Not least, it should also work for Greenplum.

The only remaining reason for this function is SECURITY DEFINER. Else you could just use the simple SQL statement (possibly as prepared statement) instead.

RETURN QUERY was added to PL/pgSQL with version 8.3 in 2008, some years after the fork of Greenplum. Might explain your error msg:

ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "QUERY" Position: 237 

Aside: LIMIT without ORDER BY produces arbitrary results. I assume you are aware of that.

If for some reason you actually need temp tables and cannot upgrade to Greenplum 5.0 like A. Scherbaum suggested, you can still make it work in Greenplum 4.3.x (like in Postgres 8.2). Use a FOR loop in combination with RETURN NEXT.
Examples:

• plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function
• How to use `RETURN NEXT`in PL/pgSQL correctly?
• Use of custom return types in a FOR loop in plpgsql