PostgreSQL – Equivalent of MySQL Query Variables

MySQLpostgresql

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL:

  1. setting variables in MySQL like

    set @aintconst = -333
set @arealconst = -9.999

    It seems not.

  2. Assigning variables from SELECT queries and using those variables subsequently in my SQL like:

     select @pfID := id from platform where bios like '%INTEL%'
 select @clientID := id from client where platformID = @pfID

I'd be very grateful for pointers, especially on (2).

Best Answer

This is easy to do inside a PL/pgSQL function (or a DO block):

create function myfunc() returns void language plpgsql as $$
  declare
    aintconst constant int = -333;
    arealconst constant real = -9.99;
    pfid int;
    clientid int;
  begin

    select id from platform where bios like '%INTEL%' into pfid;

    select id from client where platformID = pfid into clientid;

  end $$;

You can also use GUC variables:

--set a session variable
set mycustom.var = 'value';

--use it
select * from mytable where some_column = current_setting('mycustom.var');

Or you can use a CTE with a join:

with myvars as (
  select
    -333::int as aint,
    -9.99::real as areal
)

select 
  a.*
from mytable a
join myvars on true
where
  a.thing = aint
Related Question