Обсуждение: Trying to avoid a simple temporary variable declaration in a pl/pgsql function

"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

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 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.
>
>

"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