I added a 'raise notice' to a plpgsql function I was working on
recently, and noticed that my notification was being raised more often
than I expected. The notification is raised in a function ('getone'
in my example below) that returns a single composite value. This
function is then called by another function ('getset') that returns a
setof that composite value. It appears that 'getone' is called once
for each column of my composite type. I whittled this down to the
following example.
I get the expected result from my query, but I don't understand (what
appear to be) the extra function calls.
delaunay=# select * from getset( 1 );
NOTICE: id: 1, foo: afoo, bar: abar
NOTICE: id: 1, foo: afoo, bar: abar
NOTICE: id: 2, foo: bfoo, bar: bbar
NOTICE: id: 2, foo: bfoo, bar: bbar
NOTICE: id: 3, foo: cfoo, bar: cbar
NOTICE: id: 3, foo: cfoo, bar: cbar
foo | bar
------+------
afoo | abar
bfoo | bbar
cfoo | cbar
(3 rows)
CREATE TYPE mytype AS (
foo TEXT,
bar TEXT
);
CREATE TABLE dat (
set INTEGER,
id INTEGER,
foo TEXT,
bar TEXT
);
INSERT INTO dat (set, id, foo, bar)
VALUES (1, 1, 'afoo', 'abar'), (1, 2, 'bfoo', 'bbar'), (1, 3, 'cfoo', 'cbar');
CREATE OR REPLACE FUNCTION
getone(rowid INTEGER)
RETURNS mytype
AS $$
DECLARE
fooval TEXT;
barval TEXT;
BEGIN
SELECT foo, bar
FROM dat
WHERE id = rowid
INTO fooval, barval;
RAISE NOTICE 'id: %, foo: %, bar: %', rowid, fooval, barval;
RETURN ROW( fooval, barval );
END;
$$ LANGUAGE PLPGSQL STRICT;
CREATE OR REPLACE FUNCTION
getset(setid INTEGER)
RETURNS SETOF mytype
AS $$
BEGIN
RETURN QUERY
SELECT (getone(id)).*
FROM dat
WHERE set = setid;
RETURN;
END;
$$ LANGUAGE PLPGSQL STRICT;
--
Ron Peterson