Re: Function
От | bill house |
---|---|
Тема | Re: Function |
Дата | |
Msg-id | 4B8AF5E6.1050906@bellsouth.net обсуждение исходный текст |
Ответ на | Re: Function (John DeSoi <desoi@pgedit.com>) |
Ответы |
Re: Function
(bill house <wchouse@bellsouth.net>)
|
Список | pgsql-novice |
John DeSoi wrote: > On Feb 14, 2010, at 5:17 PM, bill house wrote: > >> CREATE OR REPLACE FUNCTION disp_dup_recs(varchar, varchar) >> RETURNS SETOF test_table >> AS $$ SELECT * >> FROM $1 >> WHERE $2 >> IN (SELECT $2 >> GROUP BY $2 >> HAVING ( COUNT($2) > 1 ) >> ); >> $$ >> LANGUAGE SQL >> STABLE; >> >> --returns >> --ERROR: syntax error at or near "$1" >> --LINE 81: FROM $1 > > > You can't build SQL statements like this. If you want to build a statement dynamically (where the table name and columnreferences are not known when the function is defined) you need to use EXECUTE. See > > http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > > John DeSoi, Ph.D. > > > > > Dr. John, Thanks. Studied a bit. I have taken another stab at this. The function (listed below) loads without complaint but when executed, gives me something I know not to be true: world=# select disp_dup_recs('test_table', 'field_1'); disp_dup_recs --------------- (0 rows) I have a couple of questions (besides why doesn't this function work?) 1) For debugging purposes, is there a way to see what is happening in these functions while trying them? For example, I was thinking of constructing the EXECUTE string first, echoing it to see that I did what I intended and then runnning it. 2) In the first line of the function, after the SETOF, CREATE OR REPLACE FUNCTION disp_dup_recs(z_table varchar, z_field varchar) RETURNS SETOF test_table ^^^^^^^^^^ Is there any technique to use the content of z_table to define the layout of the SETOF? At the moment, I have that format hard coded. Data and code follow: ============================================================================= -################################ records_duplicated_03.sql ############################ --Given the following file structure --The command below will return records with duplicated fields --Convert the command to a useable function --this file executed in psql by: -- \i records_duplicated_03.sql -- SELECT disp_dup_recs('test_table', 'field_1'); -- was advised that I need to use PL/pgSQL if I want to use -- variables as identifiers -- confirmed see manual page 835 section 38.1.2 /* --data set: DROP TABLE IF EXISTS test_table; CREATE TABLE test_table ( field_1 character varying (8), field_2 character varying (4), field_3 character varying (1), field_4 character varying (6), field_5 character varying (200) ); INSERT INTO test_table VALUES ('00025340', '0010', '1', 'apha 1', 'this is record #1'), ('00025340', '0010', '2', 'apha 1', 'this is record #2'), ('00025340', '0010', '3', 'apha 1', 'this is record #3'), ('00025341', '0010', '1', 'apha 1', 'this is record #4'), ('00025342', '0010', '1', 'apha 1', 'this is record #5'), ('00025342', '0010', '4', 'apha 1', 'this is record #6'), ('00025342', '0010', '1', 'apha 1', 'this is record #7'), ('00025343', '0010', '1', 'apha 1', 'this is record #8'), ('00025344', '0010', '1', 'apha 1', 'this is record #9') ; */ /* , --Command to return records with duplicate values in a field: --This command: SELECT * FROM test_table WHERE field_3 IN ( SELECT field_3 FROM test_table GROUP BY field_3 HAVING ( COUNT(field_3) > 1 ) ); -- Returns the following: field_1 | field_2 | field_3 | field_4 | field_5 ----------+---------+---------+---------+------------------- 00025340 | 0010 | 1 | apha 1 | this is record #1 00025341 | 0010 | 1 | apha 1 | this is record #4 00025342 | 0010 | 1 | apha 1 | this is record #5 00025342 | 0010 | 1 | apha 1 | this is record #7 00025343 | 0010 | 1 | apha 1 | this is record #8 00025344 | 0010 | 1 | apha 1 | this is record #9 (6 rows) */ --impliment the above as a function * CREATE OR REPLACE FUNCTION disp_dup_recs(z_table varchar, z_field varchar) RETURNS SETOF test_table AS $$ DECLARE z_table_b varchar; z_field_b varchar; BEGIN z_table_b = quote_ident(z_table); z_field_b = quote_ident(z_field); RETURN EXECUTE 'SELECT * FROM ' || z_table_b || ' WHERE ' || z_field_b || ' IN (SELECT ' || z_field_b || ' FROM ' || z_table_b || ' GROUP BY ' || z_field_b || ' HAVING ( COUNT(' || z_field_b || ' ) > 1 ));';' END; $$ LANGUAGE plpgsql; psql:records_duplicated_03.sql:102: ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT or RETURN QUERY at or near "EXECUTE" LINE 67: RETURN EXECUTE 'SELECT * FROM ' --see manual page 850: */ --invoke by: -- SELECT disp_dup_recs('test_table', 'field_1'); --see man pg 851 CREATE OR REPLACE FUNCTION disp_dup_recs(z_table varchar, z_field varchar) RETURNS SETOF test_table AS $my_dlr_quote$ DECLARE z_table_b varchar; z_field_b varchar; BEGIN --filter variables to insure necessary quoting (manual pg 847) z_table_b = quote_ident(z_table); z_field_b = quote_ident(z_field); RETURN QUERY EXECUTE 'SELECT * FROM ' || z_table_b || ' WHERE ' || z_field_b || ' IN (SELECT ' || z_field_b || ' FROM ' || z_table_b || ' GROUP BY ' || z_field_b || ' HAVING ( COUNT(' || z_field_b || ' ) > 1 ));'; END; $my_dlr_quote$ LANGUAGE plpgsql VOLATILE;
В списке pgsql-novice по дате отправления: