extra function calls from query returning composite type

From: Ronald Peterson <ron(at)hub(dot)yellowbank(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: extra function calls from query returning composite type
Date: 2014-12-29 15:17:08
Message-ID: CAJPRK8bjSioQe7OpXchG2Ka9f3Ao8FQQet7BqYsseCje25LrLw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-12-29 15:49:20 Re: Rollback on include error in psql
Previous Message Ronald Peterson 2014-12-29 15:06:41 extra function calls from query returning composite type