Trying to avoid a simple temporary variable declaration in a pl/pgsql function
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.
I thought there was an implicit variable available to me but cannot figure
out what it is nor find it in the documentation.
Using 9.3 but figuring if it is possible its likely the same in all
supported releases...
Thanks!
David J.
"David G. Johnston" <david.g.johnston@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
On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@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.
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 <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>>wrote:"David G. Johnston" <david.g.johnston@gmail.com
<mailto:david.g.johnston@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 orwithout 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
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@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.consulting@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