PostgreSQL equivalent of MySQL query variables?

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

I use WITH statements:

WITH vars as (SELECT -333::double precision as aintconst,-9.999::double precision as arealconst)
UPDATE table SET col1 = (SELECT aintconst FROM vars)

and:

WITH platformx AS (SELECT id FROM platform WHERE bios like '%INTEL%')
SELECT id FROM client WHERE platformID = (SELECT id FROM platformx)

You already answered this yourself: No, there isn't in plain SQL. You can use PL/PgSQL if you want variables, in a function or a DO block.

Most of the uses for query variables in MySQL are satisfied by CTEs (WITH queries), window functions, etc in PostgreSQL.

Well, actually there is, but they're not suitable for general use within queries. You usually access custom GUCs with SET and SHOW, but you can instead use:

regress=> select set_config('a.b', 'c', false);
 set_config 
------------
 c
(1 row)

regress=> select current_setting('a.b');
 current_setting 
-----------------
 c
(1 row)

GUCs are expensive and it's a bad idea to use this for general purpose queries, but there's very occasionally a valid use. You can only use settings like myapp.variable, too.

Tags:

Mysql

Postgresql

Recent Posts