Re: Function

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;
>
>
>
>
>
>
>
>