Turning an SQL query into a function taking identifiers

Question

I have an SQL Query in PostgreSQL to extract doy (day of year) from dates. My query is:

select extract(doy from date_col) as n
from mytable
order by n

I want to turn it into a function like extract_doy(tablename, date_column_name).
How would I do that?

Answer 1

Assuming that you want to return a setof doubles, something like this should work:

CREATE OR REPLACE FUNCTION tmp_extract_doy(table_name text, column_name text) 
RETURNS SETOF DOUBLE PRECISION
AS
$BODY$
DECLARE
BEGIN
    RETURN QUERY EXECUTE format('SELECT EXTRACT(doy from %I) AS n FROM %I ORDER BY n', column_name, table_name);
END
$BODY$
  LANGUAGE plpgsql
  COST 100;