[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. g. 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Ryan Riehle
Sent: Tuesday, March 02, 2004 11:56 AM
To: [EMAIL PROTECTED]
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. g. 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Design Problem...
Our business has multiple cost/profit centers that I call business units,
these are in a table called buinessunits. We also have a table that holds a
list of services that are offerred by a business. Each business unit has
many services it offers; 1 businees unit => Many Services. We want to be
able to query the cost/revenue/profit generated by each business unit and by
each service. The problem is that it is possible that the service can be
switched to a different business unit, and then possibly back to the
original later on. I've looked at multiple configurations, but have not
found a design that I feel is good so far. Need to somehow track when a
particular service was associated with various businessunits. If you want
more info, I can publish the tables for you. This same type of problem
exists in at least two other areas of this database we are developing.
Please help.
Kind Regards,
-Ryan
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of [EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 5:57 AM
To: [EMAIL PROTECTED]
Subject: Re: [SQL] Order by MM DD in reverse chrono order trouble
Hello,
I'd love to be able to do that, but I cannot just ORDER BY uu.add_date,
because I do not have uu.add_date in the SELECT part of the statement.
The reason I don't have it there is because I need distinct MM DD
values back. Is there a trick that I could use to make this more elegant?
Thanks,
Otis
--- Edmund Bacon <[EMAIL PROTECTED]> wrote:
> Is there some reason you can't do this:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> uu.add_date DESC;
>
> This might be faster, as you only have to sort on one field, and I
> think it should give the desired results
>
> [EMAIL PROTECTED] wrote:
>
> >Hello,
> >
> >I am trying to select distinct dates and order them in the reverse
> >chronological order. Although the column type is TIMESTAMP, in this
> >case I want only , MM, and DD back.
> >
> >I am using the following query, but it's not returning dates back in
> >the reverse chronological order:
> >
> >SELECT DISTINCT
> > date_part('year', uu.add_date), date_part('month', uu.add_date),
> > date_part('day', uu.add_date)
> >
> >FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> >ui.id=uu.user_id WHERE uus.x_id=1
> >
> >ORDER BY
> >date_part('year', uu.add_date), date_part('month', uu.add_date),
> >date_part('day', uu.add_date) DESC;
> >
> >
> >This is what the above query returns:
> >
> > date_part | date_part | date_part
> >---+---+---
> > 2004 | 2 | 6
> > 2004 | 4 |20
> >(2 rows)
> >
> >
> >I am trying to get back something like this:
> >2004 4 20
> >2004 4 19
> >2004 2 6
> >...
> >
> >My query is obviously wrong, but I can't see the mistake. I was
> >wondering if anyone else can see it. Just changing DESC to ASC, did
> >not work.
> >
> >Thank you!
> >Otis
> >
> >
> >---(end of
> broadcast)---
> >TIP 2: you can get off all lists at once with the unregister command
> >(send "unregister YourEmailAddressHere" to
> [EMAIL PROTECTED])
> >
> >
>
> --
> Edmund Bacon <[EMAIL PROTECTED]>
>
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
