How to Declare a Variable in a Postgresql Query

How to declare a variable in a PostgreSQL query

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

How to declare a variable in postgres script?

You are confused on several levels.

1. There is the query language SQL, and there is the procedural language PL/pgSQL. The only connection is that

   • you can run SQL statements from PL/pgSQL code

   • you can have PL/pgSQL code in the body of the SQL statements DO and CREATE FUNCTION/PROCEDURE.

2. There are variables in PL/pgSQL, which are defined in the DECLARE section, but there are no variables in SQL.

3. DO statements cannot return any values.

If you want to use PL/pgSQL variables, and you want to return values, you'll have to use a function. An example:

CREATE FUNCTION getpersons() RETURNS SETOF person
   LANGUAGE plpgsql AS
$$DECLARE
   overTheAgeOf int := 15;
BEGIN
    RETURN QUERY
       SELECT *
       FROM person
       WHERE age > overTheAgeOf;
END;$$;

SELECT getpersons();

There is the alternative of using variables on the client. With the psql client, you could use:

\set overTheAgeOf 15

SELECT *
FROM person
WHERE age > :overTheAgeOf;

declare variable for a postgresql query using like and union

https://www.postgresql.org/docs/current/sql-do.html

The code block is treated as though it were the body of a function
with no parameters, returning void. It is parsed and executed a single
time.

The name of the procedural language the code is written in. If
omitted, the default is plpgsql.

PL/pgsql reference: https://www.postgresql.org/docs/current/plpgsql.html

Since do command code block is returning void, we can use raise notice to extract/debug what we do. To print out something from console, generally we need some variable to hold what we want to compute/final result.
The following is an simple example to count the return query rows.

CREATE temp TABLE a1 (
    column1 text,
    column2 text,
    column3 text
);

INSERT INTO a1
    VALUES ('value', 'test', 'test1');

INSERT INTO a1
    VALUES ('misc', 'value2', 'test2');

INSERT INTO a1
    VALUES ('misc1', 'test3', 'value3');

DO $$
DECLARE
    my_var text;
    _count bigint;
BEGIN
    my_var = '%value%';
    SELECT
        count(*) INTO _count
    FROM (
        SELECT * FROM a1 WHERE column1 ILIKE my_var
        UNION
        SELECT * FROM a1 WHERE column2 ILIKE my_var
        UNION
        SELECT * FROM a1 WHERE column3 ILIKE my_var
        ) cte;
    RAISE NOTICE '_count = %', _count;
END
$$
LANGUAGE plpgsql;
  

Is there a EASY way to use a variable in a PostgreSQL query?

You can do this with a custom configuration value:

SELECT set_config('vars.find', 'search_value', false);

SELECT * FROM schema_name.table_name
   WHERE schema_name.table_name.field_name = current_setting('vars.find');

The name needs a namespace (vars in this case), but you can use any random string for this.

That last boolean parameter to set_config() determines whether the new value lasts for the rest of your session (false) or resets at the end of the current transaction (true).

How to declare a variable in a PostgreSQL query

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

Select query by declare variable on postgresql

You don't understand to DO command well. DO command is anonymous function without declaration, and because it has not declared an output, then is not possible any other result than debug stream.

so your first example has not sense in PostgreSQL. Result of unbind queries in MSSQL is returned as result of MS SQL procedure. Nothing similar is possible in PostgreSQL. PostgreSQL knows only functions, that can returns scalar value, composite value or relation (only one). When you are coming from MS SQL, the best what you can, try to forgot almost all knowleadge from MS SQL.

ERROR:  type myvar does not exist
SQL state: 42704

This bug is clean - you switch variable name and type name - really type myvar doesn't exist.

Some function that returns table can looks like:

CREATE OR REPLACE FUNCTION fx1(myvar text)
RETURNS SETOF public."PgFinalLocationsTable" AS $$
BEGIN
  RETURN QUERY SELECT * FROM public."PgFinalLocationsTable" WHERE "UserName" = myvar;
END;
$$ LANGUAGE plpgsql;

or you can use a SQL language only

CREATE OR REPLACE FUNCTION fx1(myvar text)
RETURNS SETOF public."PgFinalLocationsTable" AS $$
  SELECT * FROM public."PgFinalLocationsTable" WHERE "UserName" = $1;
$$ LANGUAGE sql;

Because PostgreSQL doesn't support unbind queries, then doesn't allow it. You should to use RETURN QUERY command - in PLpgSQL language.

Because programming with stored procedures is really different between PostgreSQL and MSSQL (MSSQL is not similar to any other), please, try to read documentation - it is not bad https://www.postgresql.org/docs/current/static/plpgsql.html

Your function can looks in Postgres like (I don't know used types)

CREATE OR REPLACE FUNCTION fx("_UserId" int,
                              "_DeviceId" int,
                              "_X" int,
                              "_Y" int, 
                              ...
BEGIN
  IF NOT EXISTS(SELECT * FROM /* I don't know what [{0}] means */
                 WHERE "UserId" = "_UserId" AND "DeviceId" = "_DeviceId")
  THEN
    INSERT INTO ..
  END IF;
END;
$$ LANGUAGE plpgsql;

Probably your fragment can be solved without procedural extension by INSERT INTO ON CONFLICT DO NOTHING command https://www.postgresql.org/docs/current/static/sql-insert.html - what is better.

Note - using case sensitive identifiers is short way to hell.

---

Related Topics