extra function calls from query returning composite type

[Ronald Peterson <ron@xxxxxxxxxxxxxxxxxx>]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general