PostgreSQL equivalent of MySQL query variables?

Question

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).

Answer 1

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

Answer 2

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)

Answer 3

PSQL Variables

Since at least version 7.1 PostgreSQL's client has provided this functionality with psql variables

\set aintconst  -333
\set arealconst -9.999

SELECT :aintconst AS aintconst, :arealconst AS realconst;
 aintconst | realconst 
-----------+-----------
      -333 |    -9.999
(1 row)

Essentially what you want is the ability to script SQL. PSQL has a conditionals and variables, and the ability to feed back dynamically generated SQL which makes this job easier. This isn't server-side functionality in the PostgreSQL world, and generally I would do this in a client language (like Node.js or Perl rather than in psql).

Answer 4

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.

Answer 5

For the second example you don't need a variable (neither in MySQL nor in Postgres):

select id 
from client 
where platformID in (select id 
                     from platform 
                     where bios like '%INTEL%');

Don't be afraid of sub-queries, Postgres' query optimizer is much smarter than MySQL's.

If the above is too slow, rewriting it into an exists query is sometimes faster:

select c.id 
from client c
where exists  (select 1
               from platform p
               where c.platformID = p.id
                 and bios like '%INTEL%');