PostgreSQL: Select dynamic column in correlated subquery

Question

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

Entity, contains the target records
Attribute, contains the column names of 'overridable' columns in the Entity table
Override, contains the EAV records

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!

Sample data and desired results would help.

Gordon Linoff
– Gordon Linoff

2018-05-10 11:03:03 +00:00
Commented May 10, 2018 at 11:03 — Gordon Linoff
– Gordon Linoff, Commented May 10, 2018 at 11:03

Vasco Grossmann · Accepted Answer · 2018-05-10 10:58:20Z

2

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;

answered May 10, 2018 at 10:58

Vasco Grossmann

1106 bronze badges

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

4 Comments

Greg Brown Over a year ago

This looks amazing, thanks so much! I have one issue though, I am getting an error "column reference id is ambiguous", I think due to SELECT id, ... FROM get_base_value(...). Should the function also need to return the entity_id as it currently only returns setof character, or the LEFT join be replace by a CROSS join?

Vasco Grossmann Over a year ago

@GregBrown Does it work if you just specify the id by changing the select statement into SELECT OV.entity_id as id, get_base_value FROM get_base_value(OV.entity_id, AT.name)?

Greg Brown Over a year ago

Yes, selecting OV.entity_id as id now works. Thanks, Vasco. Would this be better than using the CROSS join approach?

Vasco Grossmann Over a year ago

@GregBrown Great! I don't see where a CROSS JOIN could improve the performance over a LEFT JOIN. The PL/pgSQL function is necessary in any case to select the specific original columns.

Collectives™ on Stack Overflow

PostgreSQL: Select dynamic column in correlated subquery

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related