Question 108584 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to return multiple results in Postgres but I need to use a variable

I’ve got an Microsoft SQL background. So what I’m trying to do is something I know I can do in MSSql but I’m dying trying to figure out the equivalent in Postgres (most recent docker version as of the time of this post).

Here’s my MSSql.

DECLARE @id INTEGER

SELECT @id = Id FROM SomeTable where UniqueName = 'foo' // UniqueName is unique (bet you didn't guess that)

SELECT * FROM SomeTable WHERE Id = @id

SELECT * FROM AnotherTable WHERE Id = @id

SELECT * FROM FinalTable WHERE Id = @id

Is this possible?

Initially, I was trying with a CTE (equivalent):

WITH vars AS(
    SELECT Id FROM SomeTable where UniqueName = 'foo' 
)

SELECT * FROM SomeTable WHERE Id = vars.Id;

SELECT * FROM AnotherTable WHERE Id = vars.Id;

SELECT * FROM FinalTable WHERE Id = vars.Id;

but I then get an error on the 2nd query: SELECT * FROM AnotherTable saying that it doesn’t know about vars anymore.

So, Is it possible to do this in Postgres?

EDIT: So I thought this might be possible with PL/pgSQL (extensions for PostgreSQL). I tried this:

DO $$
DECLARE
    @id integer
BEGIN

    SELECT Id INTO id FROM SomeTable WHERE UniqueName = 'foo'

    SELECT * FROM SomeTable WHERE Id = id ;

    SELECT * FROM AnotherTable WHERE Id = id ;

    SELECT * FROM FinalTable WHERE Id = id ;

END $$;

but this fails to return any data (yes, I can confirm there is some data for this query) with:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 8 at SQL statement 

SQL state: 42601

so I’m really lost, now.

Tags:

2

Answers


  1. 0

    The WITH…AS construct puts the results of the query into a sort of temp table, whose results can be used in a query against another table. It’s all a single statement, though, so you can’t use the result three times like that.

    WITH vars AS(
    SELECT Id FROM SomeTable where UniqueName = 'foo' 
)
SELECT * FROM SomeTable WHERE Id in (select id from vars);

    https://www.postgresql.org/docs/current/queries-with.html

    Login or Signup to reply.
  2. 0

    Unfortunately it doesn’t seem to be possible using WITH statement, you can fix it by something like that:

    SELECT set_config('my.vars.id', Id::char, False) FROM SomeTable WHERE UniqueName = 'foo';

SELECT * FROM SomeTable WHERE Id = current_setting('my.vars.id')::int;

SELECT * FROM AnotherTable WHERE Id = current_setting('my.vars.id')::int;

SELECT * FROM FinalTable WHERE Id = current_setting('my.vars.id')::int;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search