Postgresql SELECT FROM TABLE with var

Question

I have the following SQL structure on Postgresql :

TABLE ei_1 :

╔══════╦════════╦══════╗
║ id   ║ price  ║ vat  ║
╠══════╬════════╬══════╣
║ 1    ║ 10.0   ║ 0.20 ║
║ 2    ║ 15.0   ║ 0.20 ║
║ 3    ║ 20.0   ║ 0.20 ║
║ 4    ║ 25.0   ║ 0.20 ║
╚══════╩════════╩══════╝

TABLE ei_2

╔══════╦════════╦══════╗
║ id   ║ price  ║ vat  ║
╠══════╬════════╬══════╣
║ 1    ║ 35.0   ║ 0.20 ║
║ 2    ║ 40.0   ║ 0.20 ║
║ 3    ║ 45.0   ║ 0.20 ║
║ 4    ║ 50.0   ║ 0.20 ║
╚══════╩════════╩══════╝

ei_3, ei_4 ... ei_x. The x is referenced in another table :

TABLE ei_info :

╔══════╗
║ id   ║
╠══════╣
║ 1    ║
║ 2    ║
║ 3    ║
║ 4    ║
╚══════╝

How can I select information FROM the good ei_x according to the id in ei_info ? I saw people creating temporary tables but couldn't apply this to my case.

I am try to do a SELECT * FROM ei_x

Passing a var in a SQL table name is quite easy in most coding langages, but I don't know how to do it in SQL.

Any suggestions ?

"Passing a var in SQL table name" is just not something that SQL supports. You should fix the data model so this is not needed. — Gordon Linoff
– Gordon Linoff, Commented Mar 4, 2020 at 11:52
This is a really bad data model, you should only have a single table ei that has a column to distinguish between the different "types". Do you have any chance fixing this bad design? — user330315
– user330315, Commented Mar 4, 2020 at 11:52
I don't understand what ei_info references. Does the id in there related to the id column in the other tables? And what exactly is the output you want? — user330315
– user330315, Commented Mar 4, 2020 at 11:53
Can you make ei_x a base table and then ei_1,2,3... all inherited tables? Better, add a check constraint to limit on the value of X so queries will run fast. If you have version 11, you can also use native partitioning by list values (in each case your list may only contain one) — Hambone
– Hambone, Commented Mar 4, 2020 at 12:10

pensnarik · Accepted Answer · 2020-03-04 12:21:47Z

1

As many people have told you, this is really a bad idea to do it this way unless you completely understand what you're doing. But, if you really want to do that you can write a function:

create function foo(table_index int)
  returns table (id int, price numeric, vat numeric) as $$
begin
  return query execute
    format('select id, price, vat from %I', 'ei_' || table_index::text);
end;
$$ language plpgsql;

answered Mar 4, 2020 at 12:21

pensnarik

1,2723 gold badges12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hambone · Accepted Answer · 2020-03-04 12:14:43Z

0

Elaborating on my comment, assuming you have 11 or above, you can use native partitioning:

create table ei_x (
  id integer, 
  price numeric, 
  vat numeric, 
  x integer
) partition by list (x);

create table ei_1 partition of ei_x for values in (1);

create table ei_2 partition of ei_x for values in (2);

(and so on)

At which point you can still manage the individual tables, but a query like this:

select *
from ei_x
where x = 1

Will scan only the table for the relvant values of x.

If you are on an older version of PostgreSQL, you can still use inherited tables and check constraints to accomplish the same thing. It's just more work.

answered Mar 4, 2020 at 12:14

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Collectives™ on Stack Overflow

Postgresql SELECT FROM TABLE with var

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related