current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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!

share|improve this question
    
Having the year as part of the column name is a bad idea. Read up on database normalization – a_horse_with_no_name Oct 6 at 21:09
    
@a_horse_with_no_name I know, it wasn't my first choice, but our data is really wacky. Hopefully we'll find a better way, but I'm just experimenting for now. – christopheralan88 Oct 7 at 18:30
up vote 1 down vote accepted

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;
share|improve this answer
    
Beautiful! Thank you! Also, do you know how to make this work if more than one year is present in table1? Meaning that the date_part function could return more than 1 record if the "limit 1" argument wasn't used. – christopheralan88 Oct 6 at 19:51
    
See the edited answer. – klin Oct 6 at 21:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.