6

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

9

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
  • If you use the GUC method, how do you setup a variable with an enumerated list of numbers? – user952342 Jan 2 at 18:03
8

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

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.

1

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%');
1

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

  • need updates. because latest Postgresql allow: SET LOCAL variable value – Eugen Konkov Aug 13 '18 at 14:24
  • Those are for configuration parameters -- that's a totally different thing @EugenKonkov – Evan Carroll Aug 13 '18 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.