Thread: syntax for using function parameter in to_char
I am trying to write a function that will take a big int as a paramter and return a formatted date string. The dates in the system I am using are all stored as bigint values ( based Java EPOCH ) to I need this function to make some sense of the date. Here is my code: CREATE FUNCTION java_date( bigint ) RETURNS text AS ' select to_char(\'epoch\'::datetime + \'$1 seconds\'::interval, \'dd/mm/yyyy hh:mi\') as RESULT ' LANGUAGE 'sql'; The problem seems to be that the $1 in the to_char function is not getting parsed with the actual value of $1. If I execute the following from the command line, I get the expected response: => select to_char('epoch'::datetime + '1012437127 seconds'::interval, 'dd/mm/yyyy hh:mi'); to_char ------------------30/01/2002 06:32 (1 row) All I am trying to do is move the functionality of this SQL statement to a function so I can easily reuse it. Any help would be appreciated. Kevin Barrett
Kevin, > CREATE FUNCTION java_date( bigint ) RETURNS text AS ' > select to_char(\'epoch\'::datetime + \'$1 seconds\'::interval, > \'dd/mm/yyyy hh:mi\') as RESULT > ' > LANGUAGE 'sql'; Try this instead: CREATE FUNCTION java_date( bigint ) RETURNS text AS ' select to_char(''epoch''::datetime + (CAST($1 ASVARCHAR) || ''seconds'')::interval, ''dd/mm/yyyy hh:mi'') as RESULT ' LANGUAGE 'sql'; Got it? Check, though, a "CAST (bigint as varchar)" may or may notwork. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco