Strings in UDFs - Mailing list pgsql-sql

From Ryan Riehle
Subject Strings in UDFs
Date
Msg-id 000801c40077$35e95990$9301a8c0@buildways
Whole thread Raw
In response to Re: What's wrong with my date/interval arithmetic?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Strings in UDFs
List pgsql-sql
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



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: What's wrong with my date/interval arithmetic?
Next
From: "Ryan Riehle"
Date:
Subject: Re: Strings in UDFs