5

With MSSQL it's easy, the @ marking the start of all variable names allows the parser to know that it is a variable not a column.

This is useful for things like injecting constant values where a select is providing the input to an insert table when copying from a staging table.

declare @foo varchar(50) = 'bar';
select @foo;

How do you express this for postgres?

2 Answers 2

4

PostgreSQL isn't as flexible in where and how it allows usage of variables. The closest thing for what you're trying to accomplish likely would be surrounding it in a DO block like so:

DO $$
DECLARE foo TEXT;
BEGIN
  foo := 'bar' ;
  SELECT foo;
END $$;

Note this is context dependent, and you can find more information in this StackOverflow answer.

Additionally you can create a function that declares variables and returns a value like so:

CREATE FUNCTION example_function () RETURNS text AS '
  DECLARE
    
    -- Declare a constant integer with a
    -- default value of 5.
    five CONSTANT INTEGER := 5;
    
    -- Declare an integer with a default
    -- value of 100 that cannot be NULL.
    ten INTEGER NOT NULL := 10;
    
    -- Declare a character with
    -- a default value of "a".
    letter CHAR DEFAULT ''a'';
  
  BEGIN
  return letter;
  END;

More information on this approach here.

6
  • 2
    This doesn't work, an anonymous DO block cannot return anything.
    – user1822
    Jan 15, 2021 at 6:53
  • @a_horse_with_no_name I'm not a PostgreSQL expert so I could be wrong, but the answer I linked and referenced it from with 139 upvoted is also wrong?
    – J.D.
    Jan 15, 2021 at 13:57
  • Well, that answer is wrong as well and I am surprised it got so many upvotes despite throwing the error mentioned in one of the comments: "ERROR: query has no destination for result data"
    – user1822
    Jan 15, 2021 at 14:10
  • @a_horse_with_no_name Interesting, thanks. I'll look for an alternative to correct my answer.
    – J.D.
    Jan 15, 2021 at 14:19
  • 1
    Actually it does work for my intended use case. The select statement will be providing rows to an insert statement and in fact this answer leads directly to a useful outcome, so I'll accept it provided JD qualifies it with the extra info and mentions the use case. Also, I have since found that if I create a function around the code this can't return a value BS goes away.
    – Peter Wone
    Jan 16, 2021 at 11:44
6

SQL has no support for variables, this is only possible in procedural languages (in Postgres that would e.g. be PL/pgSQL).

The way to to this in plain SQL is to use a CTE (which is also a cross platform solution and not tied to any SQL dialect):

with vars (foo) as (
  values ('bar')
)
select foo 
from vars;

like injecting constant values where a select is providing the input to an insert table when copying from a staging table.

Well you don't need a variable for that:

insert into target_table (c1, c2, c3)
select col_1, col_2, 'some value'
from staging_table;
2
  • Does your second expression work if 'some value' is text? I think it just gets interpreted as a column, no?
    – MikeB2019x
    Mar 8, 2022 at 21:58
  • 'some value' is a text (string) value
    – user1822
    Mar 8, 2022 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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