CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresplpgsql
jones-chris
jones-chris

Reputation: 691

Dynamically create column name postgresql

I'm trying to create a function that will return a table of data. The problem I'm running into is that I want some of the columns to be dynamically created based on what the year is in the Date column of the joining table.

For example, if a record's date field has a year of '2016', then I want '2016' to be concatenated with 'subdepartment_name_' to create the column name in the SELECT query.

Below is what I've written so far. 

CREATE OR REPLACE FUNCTION sample_function ()
RETURNS TABLE(subdepartment_name text,
              subdepartment_number text,
              department_number text,
              department_name text,
              my_column1 text,
              my_column2 text) AS
$$
DECLARE
    year TEXT;
BEGIN
  year := date_part('year'::text, table1.date);
  RETURN QUERY
    EXECUTE 'SELECT table2.subdepartment_number_'||year||
            ',table2.subdepartment_name_'||year||
            ',table2.department_number_'||year||
            ',table2.department_name_'||year||
            ',table1.*
            FROM
               table1
            LEFT JOIN
               table2
            ON
               table1.team = table2.team_number;';
END;
$$
  LANGUAGE plpgsql VOLATILE;

When running this function as SELECT * FROM sample_function() I get this error:

ERROR:  missing FROM-clause entry for table "table1"
LINE 1: SELECT date_part('year'::text, table1....
                                       ^
QUERY:  SELECT date_part('year'::text, table1.date)
CONTEXT:  PL/pgSQL function sample_function() line 5 at assignment

********** Error **********

ERROR: missing FROM-clause entry for table "table1"
SQL state: 42P01
Context: PL/pgSQL function sample_function() line 5 at assignment

Is there a simple way to do this that I'm missing?

Thank you!

Upvotes: 0

Views: 1500

Answers (1)

klin
klin

Reputation: 121574

The assignment

year := date_part('year'::text, table1.date);

is equivalent to

year := (select date_part('year'::text, table1.date));

As you can see there is no table1 in from clause of the query (because of lack of from clause;). You should try:

year := date_part('year'::text, table1.date) from table1 limit 1;
-- or
year := date_part('year'::text, table1.date) from table1 where id = 1;
-- or something else, the query should return exactly one row

Simple assignment needs the query to yield exactly one row. If you want to do something for a set of values use loop, e.g.:

for year in
    select distinct date_part('year', table1.date) from table1
loop
    -- do something with year
end loop;

Upvotes: 1

Related Questions