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