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) AS int4) 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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Ryan Riehle
Sent: Tuesday, March 02, 2004 11:56 AM
To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly