substring function
- Raphael
- Topic Author
- Visitor
-
12 years 5 months ago #186
by Raphael
substring function was created 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
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
Please Log in or Create an account to join the conversation.
- Raphael
- Topic Author
- Visitor
-
12 years 5 months ago #187
by Raphael
Replied by Raphael on topic Re: substring function
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.
--> 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.
12 years 5 months ago #190
by Moderator
Replied by Moderator on topic Re: substring function
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);
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.056 seconds
- You are here:
-
Home
-
Forum
-
Main PGNP Forum
-
Miscellaneous
- substring function