Return value from anonymous function postgresql

Question

How to?

For easy example. I have a simple function:

DO LANGUAGE plpgsql $$ DECLARE
BEGIN
EXECUTE 'SELECT NOW()';
END $$;

How I can return value of "NOW()" or other values from also anonymous function? The function is given as an example I have a more complex function.

score 18 · Answer 1 · answered Apr 25 '12 at 13:36

18

DO LANGUAGE plpgsql $$ DECLARE
BEGIN
execute '
create temporary table t
as
SELECT NOW()
';
END $$;

select * from t;

answered Apr 25 '12 at 13:36

Clodoaldo Neto

oh... That I need to insert or select (if insert down because pkey is duplicate). – arturgspb Apr 25 '12 at 14:34
@Arturgspb Could you edit your question with the meaning of the above comment as I don't understand it? – Clodoaldo Neto Apr 25 '12 at 15:02
How to use same block if this is used in CFQuery block with coldfusion?? – Satish Sharma Jan 29 '13 at 07:25
Oh that's genius! Finally a workaround. Postgres will be slightly less odious now. – Peter Wone Jun 09 '21 at 10:57

score 15 · Answer 2 · answered Apr 25 '12 at 12:18

15

It is not an anonymous function, but rather anonymous code block.

answered Apr 25 '12 at 12:18

vyegorov

5

I asked specifically about the anonymous functions – arturgspb Apr 25 '12 at 14:34
There're no anonymous functions as far as I'm aware. – vyegorov Apr 25 '12 at 14:49
5

@Arturgspb: ["The code block is ... returning `void`."](http://www.postgresql.org/docs/current/interactive/sql-do.html) Note the "returning `void`". – mu is too short Apr 25 '12 at 17:30
If you are worried about creating a function in a live database, consider creating a temp function: https://stackoverflow.com/questions/4990622 – Dominus.Vobiscum Nov 02 '22 at 21:31

2 Answers2