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.
2
Answers
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.https://www.postgresql.org/docs/current/queries-with.html
Unfortunately it doesn’t seem to be possible using WITH statement, you can fix it by something like that: