[SQL] Token separation
Hi, I just tried to input a hexadecimal number in PostgreSQL (8.4) and was rather surprised by the result: | tim=# SELECT 0x13; | x13 | - |0 | (1 Zeile) | tim=# SELECT 0abc; | abc | - |0 | (1 Zeile) | tim=# The documentation says: | A token can be a key word, an identifier, a quoted identifi- | er, a literal (or constant), or a special character symbol. | Tokens are normally separated by whitespace (space, tab, | newline), but need not be if there is no ambiguity (which is | generally only the case if a special character is adjacent | to some other token type). Is this behaviour really conforming to the standard? Even stranger is what MySQL (5.1.59) makes out of it: | mysql> SELECT 0x40; | +--+ | | 0x40 | | +--+ | | @| | +--+ | 1 row in set (0.00 sec) | mysql> SELECT 0abc; | ERROR 1054 (42S22): Unknown column '0abc' in 'field list' | mysql> Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Token separation
Tim Landscheidt writes: > [ "0x13" is lexed as "0" then "x13" ] > Is this behaviour really conforming to the standard? Well, it's pretty much the universal behavior of flex-based lexers, anyway. A token ends when the next character can no longer sensibly be added to it. Possibly the documentation should be tweaked to mention the number-followed-by-identifier case. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Call function with dynamic schema name
Hi to everyone, I would like to use in my function (plpgsql or sql) dynamic schema name to execute query or to call other functions. For exemple in oracle is possible to excute query in this manner: SELECT * FROM &&SCHEMA_NAME..TABLE_NAME; Where I think &&SCHEMA_NAME. is a sessione variable. I found tath I can use dynamic SQL like this: execute 'select * from ' || schema_name || '.table_name'; However, I would like to know if exist any other system to use dynamic schema name more similiar to Oracle. Another pl language is also ok. Thank you very much. Alessio -- View this message in context: http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146721.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Call function with dynamic schema name
Hello 2012/1/15 IlGenna : > Hi to everyone, > I would like to use in my function (plpgsql or sql) dynamic schema name to > execute query or to call other functions. > > For exemple in oracle is possible to excute query in this manner: > > > SELECT * FROM &&SCHEMA_NAME..TABLE_NAME; > > Where I think &&SCHEMA_NAME. is a sessione variable. > > I found tath I can use dynamic SQL like this: > > execute 'select * from ' || schema_name || '.table_name'; > > > However, I would like to know if exist any other system to use dynamic > schema name more similiar to Oracle. Another pl language is also ok. There are no similar way to Oracle. You can set a search_path variable, but you have to be careful, because cached plans in PL/pgSQL can do some issues, when function is called again with different search path. Regards Pavel Stehule > > > Thank you very much. > > > Alessio > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146721.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Call function with dynamic schema name
Can you provide me e little example plz? Thanks in advance. Alessio -- View this message in context: http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146739.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Call function with dynamic schema name
2012/1/15 IlGenna : > Can you provide me e little example plz? CREATE SCHEMA s1; CREATE SCHEMA s2; CREATE TABLE s1.a1(a int); CREATE TABLE s2.a1(a int); CREATE OR REPLACE FUNCTION s1.fx1() RETURNS int AS $$ BEGIN RETURN (SELECT MAX(a) FROM s1.a1); END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION s2.fx1() RETURNS int AS $$ BEGIN RETURN (SELECT MAX(a) FROM s2.a1); END $$ LANGUAGE plpgsql; SET search_path TO s1; SELECT fx1(); -- returns max from s1.a1; SET search_path TO s2; SELECT fx1(); -- returns max from s2.s1; Regards Pavel Stehule > > Thanks in advance. > > Alessio > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146739.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] sql query problem
Dear All, I have two tables one contains details of user and other contains result. The details are: 1. UserTable - id, name, phone 2. result - id, question_id, user_id, status (0 or 1) I want the list like this: User Id Name Attended Failed Passed but i could not find the way to do this. Please help -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sql query problem
What does a 0 state mean? Failed? And a 1 state? Passed? Best, Oliveiros 2012/1/14 Alok Thakur > Dear All, > > I have two tables one contains details of user and other contains > result. The details are: > 1. UserTable - id, name, phone > 2. result - id, question_id, user_id, status (0 or 1) > > I want the list like this: > User Id Name Attended Failed Passed > > but i could not find the way to do this. > > Please help > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] Call function with dynamic schema name
You could take a look on EXECUTE command in plpgsql... Depends on concrete problem but should be very careful with dynamic SQL because of SQL injection potential risk... Kind Regards, Misa Sent from my Windows Phone From: IlGenna Sent: 15/01/2012 18:29 To: pgsql-sql@postgresql.org Subject: [SQL] Call function with dynamic schema name Hi to everyone, I would like to use in my function (plpgsql or sql) dynamic schema name to execute query or to call other functions. For exemple in oracle is possible to excute query in this manner: SELECT * FROM &&SCHEMA_NAME..TABLE_NAME; Where I think &&SCHEMA_NAME. is a sessione variable. I found tath I can use dynamic SQL like this: execute 'select * from ' || schema_name || '.table_name'; However, I would like to know if exist any other system to use dynamic schema name more similiar to Oracle. Another pl language is also ok. Thank you very much. Alessio -- View this message in context: http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146721.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sql query problem
It seems question is not clear... I could not determine what should be in column Attended, and based on what should define passed/failed But quick tip would be SELECT name, CASE status WHEN 1 THEN 'Passed' ELSE 'Failed' END FROM UserTable INNER JOIN result ON UserTable.id = result.user_id Sent from my Windows Phone From: Alok Thakur Sent: 15/01/2012 22:08 To: pgsql-sql@postgresql.org Subject: [SQL] sql query problem Dear All, I have two tables one contains details of user and other contains result. The details are: 1. UserTable - id, name, phone 2. result - id, question_id, user_id, status (0 or 1) I want the list like this: User Id Name Attended Failed Passed but i could not find the way to do this. Please help -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
