Обсуждение: "select myfunc(fields) from my table" inside plpgslq proc

Hi all,
the following is very surprising to me.
It seems that semantic of
select INTO var myfunc(...) from ... are different in
plpgsql program than pure sql
select myfunc(...) from ...
should the former take into account only one rows
while the latter take into account all rows ?
I am sure that in past version (7.1.2 ?) both were
equivalent...

DROP   function print(int4);
CREATE function print(int4) returns bool AS'
BEGIN
  RAISE NOTICE ''print arg: %'',$1;
  RETURN true;
END;
' language 'plpgsql';

drop   table mytable;
create table mytable(num int4);

insert into mytable VALUES ('1');
insert into mytable VALUES ('2');

-- this is OK of course:
SELECT print(num) FROM mytable;

DROP   FUNCTION test();
CREATE FUNCTION test() RETURNS OPAQUE AS '
DECLARE
    lost          bool;

BEGIN
  SELECT into lost print(num) FROM mytable;

  RETURN NEW;
END;
' language 'plpgsql';

DROP   TABLE test;
CREATE TABLE test(foo int4);

CREATE TRIGGER test AFTER INSERT ON test
  FOR EACH ROW EXECUTE PROCEDURE test()
;

-- the following is surprising, comparing to
-- the previous pure SQL select:

INSERT INTO test VALUES ('1');


___________________________________________________________
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com

=?iso-8859-1?q?Jeff=20Anto?= <antojf2001@yahoo.fr> writes:
> It seems that semantic of
> select INTO var myfunc(...) from ... are different in
> plpgsql program than pure sql
> select myfunc(...) from ...

Yup.  See the docs, eg
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-statements.html#PLPGSQL-SELECT-INTO

            regards, tom lane

On Thu, 7 Mar 2002, [iso-8859-1] Jeff Anto wrote:

> Hi all,
> the following is very surprising to me.
> It seems that semantic of
> select INTO var myfunc(...) from ... are different in
> plpgsql program than pure sql
> select myfunc(...) from ...
> should the former take into account only one rows
> while the latter take into account all rows ?

Well, to be honest it should probably error if more
than one row is returned since it seems to be like
a scalar subquery in usage, but I don't think that's
what you want either.  The question is what value
from the select list is put into var when multiple
rows are returned?

> I am sure that in past version (7.1.2 ?) both were
> equivalent...

I don't have 7.1.2, but 7.1.3 seems to act like 7.2 in
this.