Reply-To: <rkr@buildways.com>
From: "Ryan Riehle" <rkr@buildways.com>
To: <pgsql-sql@postgresql.org>
Subject: Strings in UDFs
Date: Tue, 2 Mar 2004 11:55:47 -0500
Organization: BuildWays Corporation
Message-ID: <000801c40077$35e95990$9301a8c0@buildways>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <23923.1078208721@sss.pgh.pa.us>
Importance: Normal

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:=20

****************************************************************************
*
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=20