Function - Mailing list pgsql-novice

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');
===========================================================================