Asked  6 Months ago    Answers:  5   Viewed   69 times

How do I declare a variable for use in a PostgreSQL 8.3 query?

In MS SQL Server I can do this:

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

How do I do the same in PostgreSQL? According to the documentation variables are declared simply as "name type;", but this gives me a syntax error:

myvar INTEGER;

Could someone give me an example of the correct syntax?

 Answers

70

There is no such feature in PostgreSQL. You can do it only in pl/PgSQL (or other pl/*), but not in plain SQL.

An exception is WITH () query which can work as a variable, or even tuple of variables. It allows you to return a table of temporary values.

WITH master_user AS (
    SELECT
      login,
      registration_date
    FROM users
    WHERE ...
)

SELECT *
FROM users
WHERE master_login = (SELECT login
                      FROM master_user)
      AND (SELECT registration_date
           FROM master_user) > ...;
Tuesday, June 1, 2021
 
Extrakun
answered 6 Months ago
20

You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant.

DO
$$
BEGIN
EXECUTE format('CREATE TABLE %I AS SELECT * FROM backup', 'backup_' || to_char(CURRENT_DATE,'yyyy-mm-dd'));
END;
$$ LANGUAGE plpgsql;

The format(...) function's %I and %L format-specifiers do proper identifier and literal quoting, respectively.

For literals I recommend using EXECUTE ... USING rather than format(...) with %L, but for identifiers like table/column names the format %I pattern is a nice concise alternative to verbose quote_ident calls.

Friday, June 11, 2021
 
JakeGR
answered 6 Months ago
87
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2

This shows you the size of all tables in the schema public if you have multiple schemas, you might want to use:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3

SQLFiddle example: http://sqlfiddle.com/#!15/13157/3

List of all object size functions in the manual.

Saturday, July 31, 2021
 
Sergey Ryabov
answered 4 Months ago
61

Unfortunately, there are no procedural SQL extensions in Netezza that allow you to employ variables like this as part of the SQL language itself. Purely SQL solutions would involve kludges such as joining to a CTE returning that one value. However, the NZSQL CLI does allow the use of session variables, as does Aginity Workbench.

An example using NZSQL. Note the escape of the inner single quotes to use the variable as a literal.

TESTDB.ADMIN(ADMIN)=> set TVAR ''foo''
TESTDB.ADMIN(ADMIN)=> select :TVAR;
 ?COLUMN?
----------
 foo
(1 row)
TESTDB.ADMIN(ADMIN)=> create table test_table (col1 bigint);
CREATE TABLE
TESTDB.ADMIN(ADMIN)=> insert into test_table values (123);
INSERT 0 1
TESTDB.ADMIN(ADMIN)=> set TCOL 'COL1'
TESTDB.ADMIN(ADMIN)=> select :TCOL from test_table;
 COL1
------
  123
(1 row)

Aginity will auto-prompt for a values when it see $var_name, but there's no functionality to hard-code that variable definition, at least as far as I know.

Sunday, August 15, 2021
 
EurekA
answered 4 Months ago
79

I can see the following issues with that SP, which may or may not relate to your problem:

  • You have an extraneous ) after @BrandName in your SELECT (at the end)
  • You're not setting @CategoryID or @BrandName to anything anywhere (they're local variables, but you don't assign values to them)

In a comment you've said that after fixing the ) you get the error:

Procedure AddBrand has no parameters and arguments were supplied.

That's telling you that you haven't declared any parameters for the SP, but you called it with parameters. Based on your reply about @CategoryID, I'm guessing you wanted it to be a parameter rather than a local variable. Try this:

CREATE PROCEDURE AddBrand
   @BrandName nvarchar(50), -- These are the
   @CategoryID int          -- parameter declarations
AS
BEGIN
   DECLARE @BrandID int

   SELECT @BrandID = BrandID FROM tblBrand WHERE BrandName = @BrandName

   INSERT INTO tblBrandinCategory (CategoryID, BrandID) VALUES (@CategoryID, @BrandID)
END

You would then call this like this:

EXEC AddBrand 'Gucci', 23

or this:

EXEC AddBrand @BrandName = 'Gucci', @CategoryID = 23

...assuming the brand name was 'Gucci' and category ID was 23.

Friday, September 10, 2021
 
TheCarver
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share