current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

3 Answers 3

up vote 0 down vote accepted

This is easy to do inside a PL/pgSQL function:

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');
share|improve this answer
up vote 1 down vote

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.

share|improve this answer
up vote 0 down vote

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%');
share|improve this answer
    
Thanks for the performance tips. I find these nested queries (like nested if/then statements) hard to read / debug - but that's maybe just my training. – Daniel Sep 2 '14 at 15:27
    
@Daniel: if you are serious about learning SQL you should really get used to that. A single statement is almost always faster than two separate statements (not necessarily in MySQL which is optimized for many, small and simple statements. Most other DBMS have much better optimizer and perform better with fewer but more complex queries) – a_horse_with_no_name Sep 3 '14 at 6:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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