Ben Ben - 6 months ago 12
SQL Question

How to I functionally access an SQL table's table name?

I have a set of tables that I am conflating together. I want to be able to go back and access the 'raw' data later if I need to add more. The way I'm doing this by adding a reference column to the conflation table that will contain the table name of the table from which the data came from.

How can I access the names of my tables as I query from them?

EDIT: Details:

The table I'm creating looks like:

CREATE TABLE combined_things WITH OIDS AS
(SELECT
thing1.name
thing1.shape
FROM
public.thing1_source_table

UNION

SELECT
thing2.name
thing2.shape
FROM
public.thing2_source_table);


And I want to add the "source" field:

ALTER TABLE combined_things ADD COLUMN source_id character varying(100);
ALTER TABLE comnined_things SET COLUMN source_id = {table_name}


And I don't know how to pull the {table_name}

Answer

You could add them as a string constant when you create the table

CREATE TABLE combined_things WITH OIDS AS
(SELECT
thing1.name,
thing1.shape,
CAST('public.thing1_source_table' AS CHAR(100)) source_id
FROM
public.thing1_source_table

UNION

SELECT
thing2.name,
thing2.shape,
'public.thing2_source_table'
FROM
public.thing2_source_table);

Note that there is no way of casting the column to varchar