0

I don't understand why creating variables has to be so complicated in pgAdmin but can someone please help me with this concept. I would like to just create a basic variable that is a Name from a record. And then I want to search for that Name again. So here is a basic example of what I need:

VAR1 = SELECT "Name" 
FROM "Client"
WHERE "ID" = '1'; // This select will return 'Peter'

SELECT * FROM "Client" WHERE "Name" = VAR1;

I have tried all of these. Creating a temp table:

CREATE TEMP TABLE VAR1 AS VALUES (SELECT "Name" FROM "Client" WHERE "UUID" = 1;);
SELECT * FROM "Client" WHERE  Name = VAR1.column1;
...
ERROR:  syntax error at or near "SELECT"
LINE 1: CREATE TEMP TABLE VAR1 AS VALUES (SELECT "Name" FROM "Client...
                                          ^

This doesn't work in pgAdmin, it only works in psql:

\set VAR1 (SELECT "Name" FROM "Client" WHERE "ID" = '1')
SELECT * FROM "Client" WHERE name = :VAR1;

This doesn't work either:

set session my.vars.id = SELECT "Name" FROM "Client" WHERE "ID" = 1;
SELECT * FROM "Client" WHERE "Name" = current_setting('my.vars.id');
...
ERROR:  syntax error at or near "SELECT"
LINE 1: set session my.vars.id = SELECT "Name" FROM "Client" WHERE "...
                                 ^

This is not working at the moment, but it is ridiculous to expect me to write all this code for a simple VAR1 = x. Even if you get this working, I please don't want this as an answer:

DO
$$
DECLARE
  VAR1 text = (SELECT "Name" FROM "Client" WHERE "UUID" = 1);
BEGIN
  RETURN (SELECT * FROM "Client" WHERE "Name" = VAR1);
END;
$$
LANGUAGE plpgsql;
...
ERROR:  RETURN cannot have a parameter in function returning void
LINE 6:   RETURN (SELECT * FROM "Client" WHERE "Name" = VAR1);
                 ^

Using WITH doesn't work because the you can only use the variable once right below the WITH block. I want to be able to use the variable throughout the whole script.

Why does this have to be so complicated? VAR1 = x

I have read many other answers (that is where I got all of my test). So please don't reference them unless they offer something different.

Freddy Bonda
  • 1,189
  • 6
  • 15
  • 33
  • "*Why does this have to be so complicated*" - because SQL (the query language) has no concept of variables. –  Jan 17 '19 at 08:56
  • See [Is it possible to define global variables in postgresql](https://stackoverflow.com/q/31316053/1995738) – klin Jan 17 '19 at 09:00
  • @a_horse_with_no_name You marked my question is a duplicate of the other one where the `WITH` solution is the marked answer. I specifically said that the `WITH` solution doesn't work, because you can only use the `WITH` variable on query later...thanks for the explanation though – Freddy Bonda Jan 17 '19 at 09:03
  • See my answer to the duplicate: https://stackoverflow.com/a/29469454 –  Jan 17 '19 at 09:04
  • You can also use a different SQL client that provides variables –  Jan 17 '19 at 09:04
  • I did try your answer (check my question's code), can you show me how to fix it then? I am getting an error – Freddy Bonda Jan 17 '19 at 09:07

0 Answers0