Re: Strings in UDFs

Ok... just got it (finally)... pgManager output the following and it works:


****************************************************************************
*******************
CREATE FUNCTION "public"."ftcnum" (VARCHAR) RETURNS INTEGER AS'
DECLARE      maxcnt          int4 := 0;
begin

SELECT into maxcnt      CAST (          substring(                    substring( contractcode FROM $1 ||
''#"[0-9]*#"%''for 
''#'') FROM 1 for          length(          substring( contractcode FROM $1 || ''#"[0-9]*#"%'' for ''#'')          )-4)
ASint4) As contractcnt 
FROM contracts
WHERE contractcode ~* (''^'' || $1)
Order By contractcnt desc limit 1;

RETURN(maxcnt);

end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
****************************************************************************
*******************

I removed the use of quote_literal() and had to put parenthesis around the
criteria of the WHERE clause.  hrmmm... why is this?  does anyone know why
it was giving me an error (something about boolean values) when the WHERE
CLAUSE was: WHERE contractcode ~* (''^'' || $1)  ...that was what was really
messing me up before!
 -Ryan Riehle
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Ryan Riehle
Sent: Tuesday, March 02, 2004 11:56 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Strings in UDFs


UUGH.  Ok...

I am trying to write a pgsql function containing a regular expression within
a substring() function and I just can't fugure it out, and by now I've
wasted way about too much time trying.  What am I doing wrong???  I am using
the tool pgManager for debugging & it is creating this DDL in the body:

****************************************************************************
*
CREATE FUNCTION newid(VARCHAR) RETURNS INTEGER AS'

begin

SELECT INTO maxcnt      CAST (substring( substring( contractcode                       from $1 ||
quote_literal(''#"[0-9]*#"%'')                      for quote_literal(''#'')                     ) from 1 for length(
                   substring( contractcode                       from $1 || quote_literal(''#"[0-9]*#"%'')
        for quote_literal(''#''))                     )-4) AS int4) 
FROM contracts
WHERE contractcode ~* '''' || quote_literal(''^'') || $1 || '''' Order By
contractcnt desc limit 1;

RETURN(maxcnt);

end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
****************************************************************************
*

I've fooled around with it for many hours and keep getting errors relating
to arrays or booleans. grrrr. It seems weird to me that pgManager is trying
to use doubles single-quotes in the quote_literal() function when I entered
(for example) quote_literal('#') in the IDE.

Thanks for any help...

I'm using to Transact-SQL and I'm trying to make the switch to PostgreSQL;
I'm finding the whole idea of escaping the single-quotes very confusing and
so far, frustrating.  In addition to helping me with this problem, if anyone
can give me some good advice / general guidelines to using strings in my
functions, it will be greatly appreciated since I anticipate writing a lot
of these soon; it may make a big difference for me. Also, what do you
recommend as the best tool for debugging PL/pgsql functions? Does anyone
find other procedural languages more friendly (like TCL or PYTHON)?

Kind Regards,
 -Ryan Riehle


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate     subscribe-nomail command to
majordomo@postgresql.orgso that your     message can get through to the mailing list cleanly