Postgresql: Set variable equal to a specific entry in an array

Question

I have a table newb that looks something like this:

tablename | columnname
-----------------------
walls     |  id
floors    |  rowid

First I create an array:

CREATE TABLE finalsb
(
    tabnam newb ARRAY
);

Then I put data from a table into the array:

INSERT INTO finalsb VALUES(ARRAY(SELECT newb FROM newb));

The following statement displays 'id' from table newb:

SELECT tabnam[1].columnname FROM finalsb;

I want to do something like:

Declare colvar varchar,tabvar varchar
colvar = Select tabnam[1].columnname from finalsb;
tabvar = Select tabnam[1].tablename from finalsb;

My main goal is to use:

SELECT * FROM tabvar WHERE colvar = "somevalue";

Can anyone tell me how I can declare the SELECT statement as a variable?

You cannot `SELECT ... FROM` a `VARCHAR` variable (`tabvar`). What is it that you are trying to do here? Your question is not clear. — Nick, Jul 25 '16 at 20:50
I want to assign a variable to whatever value holds inside the sleect tabname[.... command. @Nicarus — nk1995coder, Jul 26 '16 at 12:45

score 0 · Accepted Answer · edited May 23 '17 at 11:51

0

You have to use select ... into variable construct:

do $$
declare
    colvar varchar;
    tabvar varchar;
begin
    select tabnam[1].columnname into colvar from finalsb;
    select tabnam[1].tablename into tabvar from finalsb;
    raise notice 'tabvar: %, colvar: %', tabvar, colvar;
end
$$;

To run dynamic sql plpgsql's execute is needed:

execute format('SELECT * FROM %s WHERE %s = "somevalue"', quote_ident(tabvar), quote_ident(colvar));

for instance in form of functions' return statement:

return query execute format('SELECT * FROM %s WHERE %s = "somevalue"', quote_ident(tabvar), quote_ident(colvar));

Recommended reading:

https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-

https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

dynamic sql query in postgres

edited May 23 '17 at 11:51

Community

1
1

answered Jul 26 '16 at 11:44

Radek Postołowicz

4,506
2
30
47

Thank you! This is what I needed!! – nk1995coder Jul 26 '16 at 12:47
I am trying to execute command: Select * from tabvar where colvar = 2786683; Basically I want to use the form Select * from tablename where columnname = 2786683. The problem is I get error relation "tabvar" does not exist. – nk1995coder Jul 26 '16 at 12:55
Did I solve your problem? Would you mind to accept answer if so? – Radek Postołowicz Aug 02 '16 at 15:57

Postgresql: Set variable equal to a specific entry in an array

1 Answers1