Using variables in a postgres sql block

Question

Can you have an SQL block that accepts a variable for input, uses that variable in a join and returns a result set.

The kicker is that I have been asked to do this outside a function - i.e. within an SQL block.

So, for example, I want to pass the value 1234567890 to the variable v_hold:

DO $$
declare
   v_hold integer;
BEGIN

 select * from t_table_A where ID = v_hold ; 
--return alert_mesg;
END$$;
--$$ LANGUAGE plpgsql

The testing I've done says that in order to return a result set, you have to define that in a RETURN TABLE declaration. I've tried to define this outside a function, but I haven't figured it out.

Can this be done outside a function - i.e pass a variable and return a result set based on a select statement which references a variable in the where clause?

The core of the problem seems to be different definitions of "SQL block". A DO command is not an "SQL block", even though its default language PL/pgSQL has "blocks" - which wouldn't be called "SQL blocks". And RETURNS TABLE (with S) is only available in a FUNCTION. The prepared statement suggested by @aengus may be the compromise. Be aware that it lives and dies with the session (unless deallocated earlier) - unlike a function, which is a persisted object. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 29, 2015 at 2:35

aengus · Accepted Answer · 2015-04-28 14:09:52Z

1

You could try using a prepared statement. For example:

    PREPARE myStatement (int) AS SELECT * FROM t_table_A where ID = $1;

To then run the statement use the execute command:

    EXECUTE myStatement(1234567890);

answered Apr 28, 2015 at 14:09

aengus

6066 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

postgres_user_99 Over a year ago

I used aengus' response - that was the best fit for me.

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

From the documentation:

DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

You could generate your code block with a shell script and get the sort of effect you are looking for.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 28, 2015 at 14:09

Politank-Z

3,7433 gold badges27 silver badges28 bronze badges

Collectives™ on Stack Overflow

Using variables in a postgres sql block

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related