How to correctly compose a postgresql query?

Question

PostgreSQL

select @rec_id="recordId" from catalog_49_links where "catalogId" = 36 and "catalogRecordId" = 352 order by "createdAt" desc limit 1;
select @new_rec_id="recordId" from catalog_49_links where "recordId"[email protected]_id and "catalogId" = 56 or ("catalogId" = 36 and "catalogRecordId" = 352) order by "createdAt" desc limit 1;

This code works in mssql studio but not in pgAdmin.
The essence of the request: get the value of the recordId field according to the conditions of the first request and then get the value of the same field, but according to the conditions of the second request, where the value from the first request is involved.

Answer 1

In MS SQL, it is customary to write in a procedural style, including using variables.
You can use PostgreSQL procedural extensions, as noted above by Sergey Gornostaev , in functions.
pgAdminIII has its own pgScript procedural extension . Please note that pgScript scripts will only work under pgAdminIII.
In pure SQL, without using variables, your query would look something like this:

with rec as (
select "recordId" as rec_id 
from catalog_49_links
 where "catalogId" = 36 and "catalogRecordId" = 352 
order by "createdAt" desc 
limit 1
)
select "recordId" as new_rec_id from catalog_49_links where "recordId" in (select rec_id from rec) and "catalogId" = 56 or ("catalogId" = 36 and "catalogRecordId" = 352) order by "createdAt" desc limit 1;

or

with rec as (
select "recordId" as rec_id 
from catalog_49_links
 where "catalogId" = 36 and "catalogRecordId" = 352 
order by "createdAt" desc 
limit 1
)
select "recordId" as new_rec_id 
from catalog_49_links
join rec on rec.rec_id = catalog_49_links."recordId"
where "catalogId" = 56 or ("catalogId" = 36 and "catalogRecordId" = 352) 
order by "createdAt" desc limit 1;

Answer 2

In PostgreSQL itself, variables can only be declared in stored procedures. But pgAdmin has pgScript which is what you need, looks like:

declare @new_rec_id;

set @new_rec_id = select recordId from catalog_49_links
where recordId in (
  select recordId from catalog_49_links
  where catalogId = 36 and catalogRecordId = 352
  order by createdAt desc
  limit 1
)
and catalogId = 56 or (catalogId = 36 and catalogRecordId = 352)
order by createdAt desc
limit 1;