Re: Function
От | bill house |
---|---|
Тема | Re: Function |
Дата | |
Msg-id | 4B8F3CC5.7050906@bellsouth.net обсуждение исходный текст |
Ответ на | Re: Function (bill house <wchouse@bellsouth.net>) |
Список | pgsql-novice |
More trials This function works given the data that follows and the hard coded last word on the declaration line (test_table). Is there any way to define the structure the setof using variable $2 (z_table)? Also, any other critiques welcome, this is my first one. Thanks Bill ============================================================================ --############## records_duplicated_05.sql ############################ --see notes after function --invoked by disp_dup_recs_05(TRUE, 'test_table', 'field_1'); CREATE OR REPLACE FUNCTION disp_dup_recs_05(debug boolean, z_table varchar, z_field varchar) RETURNS SETOF test_table AS $my_dlr_quote$ DECLARE z_table_b varchar; z_field_b varchar; z_debug boolean; exec_string 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); z_debug := TRUE; exec_string := '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 ));'; IF debug THEN RAISE NOTICE 'exec string is: %',exec_string; END IF; RETURN QUERY EXECUTE exec_string; END; $my_dlr_quote$ LANGUAGE plpgsql VOLATILE; /* Works: returns: world=# select disp_dup_recs_05(TRUE, 'test_table', 'field_1'); NOTICE: exec string is: SELECT * FROM test_table WHERE field_1 IN (SELECT field_1 FROM test_table GROUP BY field_1 HAVING disp_dup_recs_05 ------------------------------------------------ (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") (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") (6 rows) world=# select disp_dup_recs_05(FALSE, 'test_table', 'field_1'); disp_dup_recs_05 ------------------------------------------------ (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") (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") (6 rows) world=# Now to figure out how to make the main line use or not need the variables */ --############################## Data set ##################################33 --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_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) */ ============================================================================ Prior history below: not really relevant bill house wrote: > 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 column references 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 по дате отправления: