CODEWITHSUNDEEP
sqlpostgresqljoinreference
paulkon
paulkon

Reputation: 1855

Refer to table by id

Is there a way to reference a table by id. Essentially, the table contains rows which pertain to one of many tables.

e.g. I need to reference tables A or B or C in table D's rows so that I know which table to join on.

I assume that if there is no shorthand way to reference a table externally then the only option is to store the table's name.

Upvotes: 2

Views: 64

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657787

There is a "shorthand reference" for a table: the object identifier - the OID of the catalog table pg_class. Simple way to get it:

SELECT 'schema_name.tbl_name'::regclass

However, do not persist this in your user tables. OIDs of system tables are not stable across dump / restore cycles.

Also, plain SQL statements do not allow to parameterize table names. So you'll have to jump through hoops to use your idea in queries. Either dynamic SQL or multiple LEFT JOINs with CASE statements ... potentially expensive.

Upvotes: 2

Related Questions