CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sql
Greg Brown
Greg Brown

Reputation: 1358

PostgreSQL: Select dynamic column in correlated subquery

I'm using the Entity-Attribute-Value (EAV) pattern to store 'overrides' for target objects. That is, there are three tables:

What I'd like to do is select Overrides along with the value of the 'overridden' column from the Entity table. Thus, requiring dynamic use of the Attribute name in the SQL.

My naive attempt in (PostgreSQL) SQL:

SELECT
  OV.entity_id        as entity,
  AT.name             as attribute,
  OV.value            as value,
  ENT.base_value      as base_value
FROM "override" AS OV
  LEFT JOIN "attribute" as AT
    ON (OV.attribute_id = AT.id)
  LEFT JOIN LATERAL (
            SELECT
              id,
              AT.name as base_value -- AT.name doesn't resolve to a SQL identifier
            FROM "entity"
            ) AS ENT
    ON ENT.id = OV.entity_id;

This doesn't work as AT.name doesn't resolve to a SQL identifier and simply returns column names such as 'col1', 'col2', etc. rather than querying Entity with the column name.

I'm aware this is dynamic SQL but I'm pretty new to PL/pgSQL and couldn't figure out as it is correlated/lateral joined. Plus, is this even possible since the column types are not homogeneously typed? Note all the 'values' in the Override table are stored as strings to get round this problem.

Any help would be most appreciated!

Upvotes: 0

Views: 2790

Answers (1)

Vasco Grossmann
Vasco Grossmann

Reputation: 110

You can use PL/pgSQL to dynamically request the columns. I'm assuming the following simplified database structure (all original and overide values are "character varying" in this example as I didn't find any further type information):

CREATE TABLE public.entity (
   id integer NOT NULL DEFAULT nextval('entity_id_seq'::regclass),   
   attr1 character varying,   
   attr2 character varying,   
   <...>
   CONSTRAINT entity_pkey PRIMARY KEY (id) 
)

CREATE TABLE public.attribute (   
   id integer NOT NULL DEFAULT nextval('attribute_id_seq'::regclass),
   name character varying,
   CONSTRAINT attribute_pkey PRIMARY KEY (id) 
)

CREATE TABLE public.override (   
   entity_id integer NOT NULL,
   attribute_id integer NOT NULL,
   value character varying,
   CONSTRAINT override_pkey PRIMARY KEY (entity_id, attribute_id),
   CONSTRAINT override_attribute_id_fkey FOREIGN KEY (attribute_id)
      REFERENCES public.attribute (id),
   CONSTRAINT override_entity_id_fkey FOREIGN KEY (entity_id)
      REFERENCES public.entity (id))

With the PL/pgSQL function

create or replace function get_base_value(
  entity_id integer,
  column_identifier character varying
)
returns setof character varying
language plpgsql as $$
declare
begin
  return query execute 'SELECT "' || column_identifier || '" FROM "entity" WHERE "id" = ' || entity_id || ';';
end $$;

you can use almost exactly your query:

SELECT
      OV.entity_id        as entity,
      AT.name             as attribute,
      OV.value            as value,
      ENT.get_base_value  as base_value
    FROM "override" AS OV
      LEFT JOIN "attribute" as AT
        ON (OV.attribute_id = AT.id)
      LEFT JOIN LATERAL (
        SELECT id, get_base_value FROM get_base_value(OV.entity_id, AT.name)
      ) AS ENT
    ON ENT.id = OV.entity_id;

Upvotes: 2

Related Questions