substring function

General PGNP discussions.

substring function

Postby Raphael » Fri Dec 04, 2009 2:27 pm

Being new to PGNP I might be doing something wrong or misunderstand the way PGNP can be used.
After having successfully connected to a remote PostgreSQL DB and run several queries via PGNP I went for something a bit more complex.

It appears, though, as if when I used a substring function (regex), the provider failed to interpret the query and didn't return any data. The very same query works without any problem in the PostgreSQL query tool.

Query example:
SELECT cid, CAST(SUBSTRING(full_domain_name FROM '^(/.+)[/]|(/)') AS varchar) AS "domain_name", client_name, client_addr, os_type, agent_version FROM v_clients_2;

When I remove the column with the substring function from the query everything works nicely.

Is that a limitation of the trial version or are such string modification functions not supported?

Many thanks for shedding some light onto this.

Raphael
Attachments
substring.jpg
Profiler screenshot
substring.jpg (46.26 KiB) Viewed 7838 times
Raphael
 
Posts: 3
Joined: Fri Dec 04, 2009 2:12 pm

Re: substring function

Postby Raphael » Sun Dec 06, 2009 1:10 pm

After some more experimenting I found that by using a different syntax the query works fine. When not using the documented form from http://www.postgresql.org/docs/8.2/static/functions-matching.html
--> substring('foobar' from 'o.b')
but
--> substring('foobar', 'o.b')
the provider does forward the query to the db server nicely.
Raphael
 
Posts: 3
Joined: Fri Dec 04, 2009 2:12 pm

Re: substring function

Postby Moderator » Sun Dec 06, 2009 10:41 pm

Raphael, I have changed grammar to handle SUBSTRING with FROM parameter. The fix was made in build 2115 (will be publicly available shortly). Thank you!

Other supported syntaxes examples:
SELECT SUBSTRING('Test' FROM 2 FOR 2);
SELECT SUBSTRING('Test', 2, 2);
User avatar
Moderator
Site Admin
 
Posts: 302
Joined: Wed Oct 29, 2008 11:27 pm


Return to Miscellaneous

Who is online

Users browsing this forum: Yahoo [Bot] and 1 guest

cron