Skip to content

Instantly share code, notes, and snippets.

@DaveInga
Created July 24, 2013 20:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save DaveInga/6074076 to your computer and use it in GitHub Desktop.
Save DaveInga/6074076 to your computer and use it in GitHub Desktop.
I'm an sql noob, this is my first function... How do I go about creating a function 'fiscal_year' which could create output.txt (below)?
xbrl=# select fiscal_year,cik,company_name from csv_table limit 10;
fiscal_year | cik | company_name
-------------+---------+--------------------------------------
2010 | 1075607 | TC PIPELINES LP
2011 | 1075607 | TC PIPELINES LP
2012 | 1075607 | TC PIPELINES LP
2012 | 1075607 | TC PIPELINES LP
2010 | 1314054 | BMX Development Corp.
2011 | 1423974 | Farrallon, Inc
2011 | 1396536 | INFORMATION SYSTEMS ASSOCIATES, INC.
2011 | 1314054 | BMX Development Corp.
2011 | 1029802 | China Fruits Corp
2011 | 1310893 | BLUESKY SYSTEMS CORP
(10 rows)
ERROR: syntax error at or near "INTO"
LINE 5: SELECT INTO myYear
^
CONTEXT: invalid type name "INTO myYear
IF extract(month from myDate::date) > 6 THEN
extract(year from myDate::year)
ELSE
extract(year from myDate::year)-1
END IF"
CREATE FUNCTION fiscal_year(myDate TEXT) RETURNS TEXT AS
$$
BEGIN
DECLARE myYear INT;
SELECT INTO myYear
IF extract(month from myDate::date) > 6 THEN
extract(year from myDate::year)
ELSE
extract(year from myDate::year)-1
END IF;
return myYear;
END;
$$
LANGUAGE plpgsql;
xbrl=# select filing_date,cik,company_name from csv_table limit 10;
filing_date | cik | company_name
-------------+---------+--------------------------------------
04/27/2011 | 1075607 | TC PIPELINES LP
04/30/2012 | 1075607 | TC PIPELINES LP
07/30/2012 | 1075607 | TC PIPELINES LP
10/30/2012 | 1075607 | TC PIPELINES LP
05/23/2011 | 1314054 | BMX Development Corp.
08/12/2011 | 1423974 | Farrallon, Inc
08/15/2011 | 1396536 | INFORMATION SYSTEMS ASSOCIATES, INC.
08/15/2011 | 1314054 | BMX Development Corp.
08/17/2011 | 1029802 | China Fruits Corp
08/18/2011 | 1310893 | BLUESKY SYSTEMS CORP
(10 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment