Assign variable in select statement

Question

I have a table in postgres that looks like this:

In normal SQL I can declare a variable and assign it a value based on the result of a select statement:

declare @this_score int;
select @this_score = score from scores where id = 3;
print @test_int;

This outputs 42. Is it possible to assign a variable this way in postgres?

possible duplicate of [How do you use variables in a simple PostgreSQL script?](http://stackoverflow.com/questions/766657/how-do-you-use-variables-in-a-simple-postgresql-script) — raina77ow, Oct 09 '14 at 21:49
@raina77ow I don't think it's a duplicate, that question is not about assigning the variable, rather using it as a filter. I would like to actually set the variable. — Mike, Oct 09 '14 at 21:50
By "normal SQL", you appear to mean either Transact-SQL (MS SQL Server, Sybase), or MySQL (I'm not sure which). Standard SQL by itself has no notion of variables, and PostgreSQL has no scripting functionality outside of a function (or the rather limited `DO` syntax). — IMSoP, Oct 09 '14 at 23:23
Thinking about it, the semi-colons would be out of place in Transact-SQL, so I guess you're thinking of MySQL as "normal". It's really not. It's just one of many DBMS systems implementing some version of SQL, and its take on things is notorious for being non-standard and emphasising ease-of-use over robustnees. — IMSoP, Oct 09 '14 at 23:30

score 3 · Accepted Answer · answered Oct 10 '14 at 02:29

PostgreSQL's SQL dialect isn't extended with scripting features; instead, it expects you to use PL/PgSQL. In that regard it's more like Oracle than Microsoft's T-SQL or MySQL.

Use a DO block to run PL/PgSQL, or create and run a function if you want a return value.

e.g.

DO
$$
DECLARE
    this_score int;
BEGIN
    SELECT score FROM scores WHERE id = 3 INTO this_score;
    RAISE NOTICE 'this_score is: %', this_score;
END;
$$;

Assign variable in select statement

1 Answers1

Related