substring function

More
7 years 9 months ago #186 by Raphael
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:

Please Log in or Create an account to join the conversation.

More
7 years 9 months ago #187 by Raphael
After some more experimenting I found that by using a different syntax the query works fine. When not using the documented form from [url:aa94lq58]http://www.postgresql.org/docs/8.2/static/functions-matching.html[/url:aa94lq58]
--> substring('foobar' from 'o.b')
but
--> substring('foobar', 'o.b')
the provider does forward the query to the db server nicely.

Please Log in or Create an account to join the conversation.

More
7 years 9 months ago #190 by Moderator
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);

Please Log in or Create an account to join the conversation.

Time to create page: 0.164 seconds
Powered by Kunena Forum