Re: [GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
David G. Johnston david.g.johns...@gmail.com writes: I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use RETURN expression if this is all that will be in the function. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane t...@sss.pgh.pa.us wrote: David G. Johnston david.g.johns...@gmail.com writes: I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use RETURN expression if this is all that will be in the function. The use of SELECT is required and will likely have a CTE and a set of SQL CASE expressions as part of it. It isn't a problem to declare it myself but I thought I had read about there being an implicit variable name that could be used instead. I guess I mis-remembered... Thanks for the quick response. David J.
Re: [GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
David G. Johnston david.g.johns...@gmail.com writes: On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane t...@sss.pgh.pa.us wrote: David G. Johnston david.g.johns...@gmail.com writes: I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use RETURN expression if this is all that will be in the function. âThe use of SELECT is required and will likely have a CTE and a set of SQL CASE expressions as part of it. â It isn't a problem to declare it myself but I thought I had read about there being an implicit variable name that could be used instead. I guess I mis-remembered... Try this... sj$ psql -eqf q begin; create table foo as select 'here goes some text'::text as tf; create function foo () returns text as $$ begin return case when true then tf end from foo limit 1; end $$ language plpgsql; select foo(); foo - here goes some text (1 row) abort; sj$ HTH âThanks for the quick response. David J. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
Hello The solution proposed by Tom works as long as you can make sure that your SELECT statement in the function will return a single row with a single column of type TEXT: CREATE TABLE test (id INTEGER, what_goes_here TEXT); INSERT INTO test values (1,'Text 1'); INSERT INTO test values (2,'Text 2'); CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN RETURN (SELECT what_goes_here FROM test LIMIT 1); END; $$; SELECT * FROM test_func(); test_func --- Text 1 (1 row) No need for INTO. Bye Charles On 6/20/2015 17:07, David G. Johnston wrote: On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane t...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.uswrote: David G. Johnston david.g.johns...@gmail.com mailto:david.g.johns...@gmail.com writes: I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use RETURN expression if this is all that will be in the function. The use of SELECT is required and will likely have a CTE and a set of SQL CASE expressions as part of it. It isn't a problem to declare it myself but I thought I had read about there being an implicit variable name that could be used instead. I guess I mis-remembered... Thanks for the quick response. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
