Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function

Function

From
bill house
Date:
Hello,

Trying functions out and am at a loss at where I am going wrong.

This file contains some sample data, scripts and results of some failed
attempts:  Thanks in advance.

Bill House

======================================================================

-################################ records_duplicated_01.sql ###########
--Given the following file structure
--The command below will return records with duplicated fields
--Convert the command to a usable function
--this file executed in psql by:

--       \i records_duplicated_01.sql


/*

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)

*/



--implement the above as a function

/*
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
*/


/*
CREATE OR REPLACE FUNCTION
   disp_dup_recs(z_table varchar, z_field varchar)
   RETURNS SETOF test_table
   AS $$ SELECT *
         FROM z_table
         WHERE z_field
         IN (SELECT z_field
             FROM z_table
             GROUP BY z_field
             HAVING ( COUNT(z_field) > 1 )
            );
       $$
     LANGUAGE SQL
     STABLE;


--Returns
--ERROR:  relation "z_table" does not exist
--CONTEXT:  SQL function "disp_dup_recs"
*/


-- When the above errors are resolved,
--   the function should be invoked by:

--  SELECT disp_dup_recs('test_table', 'ndb_no');
===========================================================================

Re: Function

From
John DeSoi
Date:
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
referencesare 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.

Re: Function

From
John DeSoi
Date:
On Feb 20, 2010, at 9:33 AM, bill house wrote:

>> 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
>>
> Thanks for the help.  Having reread that part of the manual, I see what you are talking about.
>
> I am running 8.3, I see all of the documentary references are referring to 8.4.
>
> My attempt to upgrade to 8.4 did not go smoothly.  Is this the right forum to ask about this issue?

Yes, this is the right forum. Be sure to reply-all so that your responses go back to the list.

The 8.3 documentation is online as well:

http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN




John DeSoi, Ph.D.

Re: Function

From
bill house
Date:
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;

Re: Function

From
bill house
Date:
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;
>
>
>
>
>
>
>
>