CODEWITHSUNDEEP
sqlpostgresqlcrosstab
Bannor
Bannor

Reputation: 35

Query with dates as column headers within user-specified range

I'm trying to generate a query in Postgres 9.1 which will generate as many columns as needed to suit a date range. The function below generates the SQL string I need, however I cannot then execute the SQL which the function returns. The result needs to look like a single (or in future multiple) descriptor columns, followed by as many columns as are necessary to make up the date range. The headers of these fields will be dates, while the contents of the records for these fields will be text.

I'm not sure if I'm on the right track with this approach, so if there is a simpler way then I'd prefer to take it. Anyt help would be appreciated.

CREATE OR REPLACE FUNCTION admin."TV_EmpStatusSQL_func" (
  "StartDate" date = 'now'::text::date,
  "EndDate" date = 'now'::text::date + 7
)
RETURNS text AS
$body$
DECLARE
  dateval DATE;
  sqlstring TEXT;
  StartDate ALIAS FOR $1;
  EndDate ALIAS FOR $2;
BEGIN

sqlstring := 'SELECT admin."HR_Employees_view"."EmployeeName"';
dateval := StartDate;

WHILE dateval <= EndDate LOOP
sqlstring := sqlstring || ', admin."TV_WhereIsEmployee_func"(admin."HR_Employees_view"."HRE_ID",     ''' || dateval || ''') AS "' || dateval || '"';
dateval := dateval + 1;
END LOOP;
sqlstring := sqlstring || ' FROM admin."HR_Employees_view";';
RETURN sqlstring;
END;
$body$
LANGUAGE 'plpgsql'
STABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Upvotes: 0

Views: 245

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

Use execute inside a plpgslql function to execute dynamic sql

execute sqlstring;

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Upvotes: 1

Related Questions