X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CC6EBD1ED61 for ; Fri, 26 Mar 2004 14:16:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 04365-03 for ; Fri, 26 Mar 2004 10:16:01 -0400 (AST) Received: from zamail.onsite.hosting.co.za (zamail.onsite.hosting.co.za [196.35.79.58]) by svr1.postgresql.org (Postfix) with SMTP id C53E2D1ED5C for ; Fri, 26 Mar 2004 10:15:57 -0400 (AST) Subject: date_part stored procs Reply-To: From: "Tsoloane Moahloli" To: Date: Fri, 26 Mar 2004 16:19:11 +0200 Organization: Mimecast South Africa Message-ID: <000001c4133d$51447430$7b35a8c0@devel> MIME-Version: 1.0 X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C4134E.14CECAD0" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=BAYES_44, HTML_FONTCOLOR_RED, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200403/254 X-Sequence-Number: 17353 ------=_NextPart_000_0001_01C4134E.14CECAD0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Making me lose my cool. Here is how it goes: I have the following Stored Proc, Which I intend to find the age in months of a date... CREATE FUNCTION interval_months(TIMESTAMP) RETURNS INTEGER AS ' DECLARE docDate ALIAS FOR $1; len INTEGER; BEGIN SELECT INTO len * FROM (SELECT ((date_part(''year'',age(docDate))*12)+(date_part(''month'',age(docDate)))) AS a; RETURN len; END ' LANGUAGE 'plpgsql'; The problem is that it does not run and I cannot for the life of me tell why. I run it with the following in psql: select interval_months(file_date) from (select min(file_date) as file_date from fileattributes) AS a; And I get the folllowing error: NOTICE: Error occurred while executing PL/pgSQL function interval_months NOTICE: line 5 at select into variables ERROR: parser: parse error at or near "" I can't see the error, I've lost my mind.... It's friday........ Tsoloane Moahloli Verity South Africa (Pty) Ltd P +27 (11) 475 1718 C +27 (83) 400-2998 tsoloane@verity.za.com http://www.verity.com/ Over 80% of the FORTUNE 50 trust Verity to power their Business Portal and e-Commerce sites. Verity is the leader in the Gartner 2002 Enterprise Search Magic Quadrant Disclaimer The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Views and opinions are those of the sender unless clearly stated as being that of Mimecast (Pty) Ltd. Mimecast (Pty) Ltd is neither liable for the proper, complete transmission of the information contained in this communication, nor any delay in its receipt or that the mail is virus-free. Mimecast (Pty) Ltd is not liable whatsoever for loss or damage resulting from the opening of this message and/or attachments and/or the use of the information contained in this message and/or attachments. ------=_NextPart_000_0001_01C4134E.14CECAD0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
Mak= ing me lose my=20 cool.  Here is how it goes:
 
I h= ave the following=20 Stored Proc, Which I intend to find the age in months of a=20 date...
 
 
CRE= ATE FUNCTION=20 interval_months(TIMESTAMP) RETURNS INTEGER AS=20 '
 DECLARE
  docDate ALIAS FOR $1;
  len= =20 INTEGER;
 BEGIN
  SELECT INTO len * FROM (SELECT=20 ((date_part(''year'',age(docDate))*12)+(date_part(''month'',age(docDate))))= AS=20 a;
  RETURN len;
 END
' LANGUAGE=20 'plpgsql';
 
The= problem is that=20 it does not run and I cannot for the life of me tell why.   = =20
 
 
I r= un it with the=20 following in psql:  
 
sel= ect=20 interval_months(file_date) from (select min(file_date) as file_date from=20 fileattributes) AS a;
 
And= I get the=20 folllowing error:
NOT= ICE:  Error=20 occurred while executing PL/pgSQL function interval_months
NOTICE: = line=20 5 at select into variables
ERROR:  parser: parse error at or near= =20 ""
 
I c= an't see the=20 error, I've lost my mind....  It's friday........
 

Tsoloane Moahloli
Verity South=20 Africa (Pty) Ltd
P +27 (11) 475 1718
C +27 (83)=20 400-2998
tsoloane@verity.za.com

http://www.verity.com/Over 80% of the FORTUNE 50 trust Verity to power their Bu= siness Portal=20 and e-Commerce sites.

Verity is the leader in the Gartner 2002 Enter= prise=20 Search Magic Quadrant

 

Disclaimer

The information contained in this commun= ication is=20 confidential and may be legally privileged. It is intended solely for the u= se of=20 the individual or entity to whom it is addressed and others authorized to= =20 receive it. If you are not the intended recipient you are hereby notified t= hat=20 any disclosure, copying, distribution or taking action in reliance of the= =20 contents of this information is strictly prohibited and may be unlawful. Vi= ews=20 and opinions are those of the sender unless clearly stated as being that of= Mimecast (Pty) Ltd. Mimecast (Pty) Ltd is neither liable for the proper,=20 complete transmission of the information contained in this communication, n= or=20 any delay in its receipt or that the mail is virus-free. Mimecast (Pty) Ltd= is=20 not liable whatsoever for loss or damage resulting from the opening of this= =20 message and/or attachments and/or the use of the information contained in t= his=20 message and/or attachments.

------=_NextPart_000_0001_01C4134E.14CECAD0--