Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g11Hxgl21463 for ; Fri, 1 Feb 2002 12:59:42 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g11Hv5557854 for pgsql-sql@postgresql.org; Fri, 1 Feb 2002 12:57:05 -0500 (EST) (envelope-from news) From: Kevin Barrett X-Newsgroups: comp.databases.postgresql.sql Subject: syntax for using function parameter in to_char Date: Fri, 1 Feb 2002 11:57:03 -0600 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 36 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit X-Complaints-To: news@hub.org User-Agent: KNode/0.4 To: pgsql-sql@postgresql.org X-Archive-Number: 200202/17 X-Sequence-Number: 6449 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