> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane > Sent: Monday, May 30, 2011 11:10 PM > To: David Johnston > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Function Column Expansion Causes Inserts To Fail > > "David Johnston" <polobo@xxxxxxxxx> writes: > > SELECT ( createpkrecord('1') ).*; > > [ results in function being called more than once ] > > Yeah. Don't do that. Better style is > > SELECT * FROM createpkrecord('1'); > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >From syntax works fine for literals but how would you then get table.column values into the function call - where you want to evaluate multiple rows from the source table? In order to feed rows to a function you need the function in the SELECT column-list so that it can see the columns in question. If using the described syntax results in the odd situation where a function is called twice I would consider that a bug. Either it needs to be fixed or the system should disallow that syntax from being used. I haven't tried serial pk creation or other side-effects that would not result in such an obvious error but it is reasonable to believe that if the duplicate key exception is being thrown then other bad - but not catchable things - could occur as well. Even an expensive SELECT statement inside the function would make this behavior undesirable - though I am guessing it would otherwise be invisible since the SELECT is not a side-effect and thus the engine would only return one set of results - though I haven't tested this theory either. The fact that: SELECT createpkrecord('1') works - returning a "row" - leads me to think that decomposing that row should be (but is not) independent of the source of that "row". The work around I described (converting the SELECT function() statement to a sub-query and expanding the results in the parent) is fine but if that is the only safe way to do it then the alternate method should fail since it is unsafe. Now, back to my first question, are there other alternatives that I've overlooked when you want to use the result of a SELECT statement as the source of values for a function call? That is, how would you re-write this to place "createpkrecord(sub)" in a FROM clause instead of the SELECT list? SELECT createpkrecord(sub) FROM (SELECT sub FROM generate_series(1, 10) sub ) src; David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
