Forum OpenACS Development: Response to SQL functions returning rowsets (of one type)

Collapse
Posted by Don Baccus on
Since the exec string is treated as a vanilla query, the "INTO" actually creates a temp table just as it does from PSQL, rather than into a local PL/pgSQL variable. If it's been disabled it was probably to avoid the confusion (I remember that the user triggering this discussion thought it really was going "INTO" their local variable).

In other words, in the PG query language:

select * into foo from bar;

copies the relation bar into the temporary relation foo.

As far as passing a table in, something like this works:

create function foo(relation_name) returns integer as '
declare
  r alias for $1;
begin
  return r.column_name;
end;' language 'plpgsql';

select foo(relation_name) from relation_name where key = key_value;

The problem I was alluding in my earlier post is that this doesn't work for children of relation_name, only for relation_name itself. It appears to be an oversight but won't be fixed for 7.1, that's certain. If you pass in a relation which inherits from relation_name, the function works but gets an error on its return claiming it is returning a relation_name of the wrong size. This is because the relationship is passed by reference, with the size check on the return done on the declared relation type rather than the actual type of the relation passed in. Oops.

Thus my meandering thoughts that one could use an untyped RECORD ala new/old in triggers to write functions that work on mulitple types that happen to have column names in column (as is true in the inheritance example).

Can't do that, though, the trigger parameters are passed by special magic internal to Postgres. You can't declare a parameter to be of type RECORD.