Whether prompt please whether in Postgre SQL 8.4 to use variables in requests (not in functions)? rummaging in the internet, I understand that they are not ((

so I decided to cheat and use a temporary table to take the necessary parameters from it

For example:

CREATE TEMP TABLE tempp ON COMMIT DROP AS SELECT 'test.ss' AS imptable, --Π’Π°Π±Π»ΠΈΡ†Π° 'company_id' AS column1 --столбСц ; SELECT * FROM (SELECT imptable FROM tempp) a where (SELECT column1 FROM tempp) >1;

I thought it would work, and it will look in test.ss, but no:

 SELECT * FROM (SELECT imptable FROM tempp)

makes a choice not from test.ss but from tempp , because in fact he sees my subquery as a table, and not as a word that I want to substitute into the script

I want to get the result as if I wrote

 SELECT * FROM test.ss

Tell me what to do if there are normal variables, without using functions? If not, how else can you use something like variables in queries, change something in one place, so as not to edit the whole query

  • What's wrong with common table expressions ? - D-side
  • using WITH I get the same result, I want to substitute the name of the table in the script, and use a subquery to the temporary building where this name is spelled, but it takes my subquery with the name as a table, and not as a word I would like to use - nonament
  • Give a minimal example of data, expected and actual results. - D-side
  • Corrected the description, chewing everything as much as possible - nonament
  • Mm. You store identifiers in a temporary table that you want to use when creating the final query without knowing them in advance. This is a strange desire in general and usually solved at the client level, but now at least it is clear what the essence is. And why CTE is not suitable. - D-side

1 answer 1

  UPDATE "STA_User" b SET "ID" = a."ID", "Username"=a."Username" FROM ( SELECT * FROM dblink('port=5432 dbname=Bekap user=postgres password=123456' , 'SELECT "ID", "Username" FROM public."STA_User"') AS t("ID" INTEGER, "Username" TEXT) )a WHERE b."ID" = a."ID"; -- ΠžΠ±Π½ΠΎΠ²ΠΈΡ‚ΡŒ Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ с Ρ‚Π°ΠΊΠΈΠΌΠΈ ΠΆΠ΅ ID ΠΊΠ°ΠΊ Ρƒ исходной

'a' and 'b' preremnnye