function refused
От | Jean-Yves F. Barbier |
---|---|
Тема | function refused |
Дата | |
Msg-id | 20111107015448.6a29be1b@anubis.defcon1 обсуждение исходный текст |
Ответы |
Re: function refused - oops: forget
("Jean-Yves F. Barbier" <12ukwn@gmail.com>)
|
Список | pgsql-novice |
Hi list, I try to put a query that works on command line into a sql function but I get this: =# \i /OTHER/PRJ_COMGEST/DB_PROCS/004_E_SCH_TAB_COL.sql psql:/OTHER/PRJ_COMGEST/DB_PROCS/004_E_SCH_TAB_COL.sql:46: ERROR: return type mismatch in function declared to return record DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING. CONTEXT: SQL function "e_sch_tab_col" psql:/OTHER/PRJ_COMGEST/DB_PROCS/004_E_SCH_TAB_COL.sql:48: ERROR: function e_sch_tab_col(text, text, text) does not exist ********************** Fn: CREATE OR REPLACE FUNCTION e_sch_tab_col(TEXT, -- 1: Owner TEXT, -- 2: Schema TEXT) -- 3: Table RETURNS SETOF RECORD AS $$ SELECT C.relname, A.attname FROM pg_class C INNER JOIN pg_user U ON C.relowner = U.usesysid INNER JOIN pg_attribute A ON A.attrelid = C.relfilenode WHERE C.relname !~ '^(pg_|sql_)' AND C.relkind = 'r' AND A.attnum > 0 AND C.relowner = (SELECT e_usr_oid(''||$1||'')) AND C.relnamespace = (SELECT schoid FROM (SELECT * FROM e_sch() AS z(schoid OID, schname NAME) WHERE schname = ''||$2||'') AS schemaoid) AND C.relname = ''||$3||'' ORDER BY A.attrelid, A.attnum; END; $$ LANGUAGE sql STRICT SECURITY DEFINER STABLE; --============================================================================= REVOKE ALL ON FUNCTION e_sch_tab_col(TEXT, TEXT, TEXT) FROM public; ********************** Command Line: =# SELECT C.relname, A.attname FROM pg_class C INNER JOIN pg_user U ON C.relowner = U.usesysid INNER JOIN pg_attribute A ON A.attrelid = C.relfilenode WHERE C.relname !~ '^(pg_|sql_)' AND C.relkind = 'r' AND A.attnum > 0 AND C.relowner = (SELECT e_usr_oid('dbowner')) AND C.relnamespace = (SELECT schoid FROM (SELECT * FROM e_sch() AS z(schoid OID, schname NAME) WHERE schname = 'common') AS schemaoid) AND C.relname = 'town' ORDER BY A.attrelid, A.attnum; relname | attname ---------+-------------- town | id town | label town | date_cre town | date_mod town | ri_users_cre town | ri_users_mod (6 rows) I understand RETURNS SETOF RECORD AS isn't accepted, although function seems to issue a record (?); and I've almost the same function that works with this kind of RETURNS. You know what? SQL is sometimes hard to understand! JY -- Big M, Little M, many mumbling mice Are making midnight music in the moonlight, Mighty nice!
В списке pgsql-novice по дате отправления: