Function

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