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(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>wrote:
>
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com
> <mailto:david(dot)g(dot)johnston(at)gmail(dot)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.
>
>