I have the following problem
I want to use using a function on result of subselect:
I create the following function:
hannu=# create or replace function pg_fields(pg_user) returns text as '
hannu'# tup = args[0]
hannu'# return tup["usename"] + ":" + str(tup["usesysid"])
hannu'# ' LANGUAGE 'plpython';
CREATE FUNCTION
And it runs fine straight on table/view:
hannu=# select pg_fields(pg_user) from pg_user;pg_fields
------------postgres:1hannu:100
(2 rows)
But I am unable to run it on a subselect, whatever I do:
hannu=#
hannu=# select pg_fields(pg_user) from (select * from pg_user) as
pg_user;
ERROR: Cannot pass result of sub-select or join pg_user to a function
hannu=#
hannu=# select pg_fields(pg_user) from (select pg_user from pg_user) as
pg_user;
ERROR: You can't use relation names alone in the target list, try
relation.*.
hannu=# select pg_fields(pg_user) from (select pg_user.* from pg_user)
as pg_user;
ERROR: Cannot pass result of sub-select or join pg_user to a function
I there a way to:
a) tell PostgreSQL that the funtion can take any row type as an argument
or
b) to cast the result of subquery to a known row type
--
Hannu Krosing <hannu@tm.ee>