Created
July 24, 2013 20:10
-
-
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)?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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