Обсуждение: variables in ad hoc queries
If I have a function that returns an integer, how can I use the following code outside of a function block? declare _myint int; begin _myint := myfunction( ); end; or is it just not possible? Iain
On Tue, Sep 29, 2009 at 8:06 AM, Iain Barnett <iainspeed@gmail.com> wrote: > If I have a function that returns an integer, how can I use the following > code outside of a function block? > > declare > _myint int; > > begin > _myint := myfunction( ); > end; you can...all postgresql functions can be used in queries: select * from myfunction(); What you are probably bumping into is that previously to 8.4, plpgsql functions could not be called the short way: select myfunction(); If this is your problem, the workarounds are to adjust the query, wrap it with an sql function, or upgrade to 8.4. merlin
On 29/09/2009 13:06, Iain Barnett wrote: > If I have a function that returns an integer, how can I use the > following code outside of a function block? > > declare > _myint int; > > begin > _myint := myfunction( ); > end; You can't use this code outside of a function - this just isn't valid SQL. You can of course use your function - select myfunction(); - though I'm sure you know that already. What exactly are you trying to achieve? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
2009/9/29 Iain Barnett <iainspeed@gmail.com>: > If I have a function that returns an integer, how can I use the following > code outside of a function block? > > declare > _myint int; > > begin > _myint := myfunction( ); > end; > select myfunction from myfunction(); or, you can use the function in SQLs wherever you'd usually use an integer > or is it just not possible? > > Iain > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/
Sorry, in oversimplifying for clarity I was making it more unclear! Whenever I write a function, I like to write a quick ad hoc test to go with it in the comments. It means I can also test combinations of functions together quickly, without having to build other functions first, and gives anyone reading the function a quick idea of the type of inputs/outputs they might see. I've mostly worked with SQL Server, and with that I can write statements that might need to be within a function in ad hoc fashion. A common example is when I write an insert statement that returns the primary key of the inserted row. I'd like to capture that in a variable and run it through other functions. To be able to declare variables outside a function would be helpful in this respect. It's just a quick way to an easy test, in my view, but if it's not possible then that's ok, I'll just write full blown test functions from the start. Regards, Iain