How do you use variables in a simple PostgreSQL script?

Question

For example, in MS-SQL, you can open up a query window and run the following:

DECLARE @List AS VARCHAR(8)

SELECT @List = 'foobar'

SELECT *
FROM   dbo.PubLists
WHERE  Name = @List

How is this done in PostgreSQL? Can it be done?

Answer 1

For the official CLI client "psql" see here. And "pgAdmin3" 1.10 (still in beta) has "pgScript".

Answer 2

Here's an example of using a variable in plpgsql:

create table test (id int);
insert into test values (1);
insert into test values (2);
insert into test values (3);

create function test_fn() returns int as $$
    declare val int := 2;
    begin
        return (SELECT id FROM test WHERE id = val);
    end;
$$ LANGUAGE plpgsql;

SELECT * FROM test_fn();
 test_fn 
---------
       2

Have a look at the plpgsql docs for more information.

Answer 3

Even I do have the same problem, the problem is we wont use procedures or functions to achieve the above query by just using the SQL stmt. Note that we should not use procedures or functions. Can any one plz temme the solution for the above problem.

Answer 4

I've came across some othere documents which they use \set to declare scripting variable but the value is seems to be like constant value and I'm finding for way that can be acts like a variable not a constant variable.

Ex:

\set Comm 150

select sal, sal+:Comm from emp

Here sal is the value that is present in the table 'emp' and comm is the constant value.

Answer 5

I have the same issue...

In SQL you use the following script to declare a set date and can then use the variable in you script.

DECLARE @BeginDate DATETIME
    SET @BeginDate = '2010/01/01'

You can then use @BeginDate in your query.... Anyone with a quick tip?