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.
