Re: Function

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;