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:06:41
Message-ID: CAJPRK8ZShSUcYonoARoU_GVCiz_D41gbDEPD_-p7TCh1cXJ5Tw@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'd expect. 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 it appears to me (and
what do I know) that perhaps something inefficient is happening in the
way this query is running. Or maybe this is doing exactly what it
should, in which case this is just a curiosity question - I don't
understand why my 'getone' notice is called twice as often as I'd
expect.

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 Ronald Peterson 2014-12-29 15:17:08 extra function calls from query returning composite type
Previous Message Kevin Grittner 2014-12-29 15:00:11 Re: help troubleshooting invalid page header error