Skip site navigation (1) Skip section navigation (2)

Re: Function

From: bill house <wchouse(at)bellsouth(dot)net>
To: John DeSoi <desoi(at)pgedit(dot)com>
Cc: psql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Function
Date: 2010-03-04 04:53:25
Message-ID: 4B8F3CC5.7050906@bellsouth.net (view raw or flat)
Thread:
Lists: 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;
> 
> 
> 
> 
> 
> 
> 
> 


In response to

pgsql-novice by date

Next:From: Giovanni GiganteDate: 2010-03-04 19:15:46
Subject: a query problem
Previous:From: Dara OlsonDate: 2010-03-03 23:17:33
Subject: sql query on multiple contains

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group