Re: Function - Mailing list 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;