From: Ryan Riehle <rkr@buildways.com>
To: pgsql-sql@postgresql.org
Subject: Re: Strings in UDFs
Date: Tue, 2 Mar 2004 12:22:08 -0500
Message-ID: <000001c4007a$e45ee190$9301a8c0@buildways> (raw)
In-Reply-To: <000801c40077$35e95990$9301a8c0@buildways>
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@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.org so that your
message can get through to the mailing list cleanly
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: <000001c4007a$e45ee190$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