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
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 |