Re: Trying to avoid a simple temporary variable declaration in a pl/pgsql function

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:

> On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "David G. Johnston" <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...

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(dot)consulting(at)comcast(dot)net
p: 312.241.7800