From: Ryan Riehle <rkr@buildways.com>
To: pgsql-sql@postgresql.org
Subject: Strings in UDFs
Date: Tue, 2 Mar 2004 11:55:47 -0500
Message-ID: <000801c40077$35e95990$9301a8c0@buildways> (raw)
In-Reply-To: <23923.1078208721@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rkr@buildways.com
Subject: Re: Strings in UDFs
In-Reply-To: <000801c40077$35e95990$9301a8c0@buildways>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox