Re: [SQL] dynamic columns in a query
Hello 2009/6/11 Jyoti Seth : > Hi All, > > Is there any way in postgres to write a query to display the result in > matrix form. (where column names are dynamic) > look on http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html regards Pavel Stehule > For eg. > > > Employee Name Client1 Client2 Client3 Client4 > Emp1 100 102 90 23 > Emp2 56 0 23 98 > Emp3 34 45 76 0 > > > Here Client1, Client2... are the values from the database. > > Thanks, > Jyoti > > > > -- > 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] skip if latter value equal
Hello you can do it simply in new PostgreSQL 8.4. In older version the best way what I know is using a stored procedure, that returns table create or replace function foo() returns setof yourtablename as $$ declare r yourtablename; s yourtablename; result youratblename; first boolean = true; begin for r in select * from yourtablename loop order by ... if first then return next r; s := r; first := false; else if r.a is distinct from s.a then result.a := r.a else result.a := NULL end if; if r.b is distinct from s.b then result.b := r.b else result.b := NULL end if; if r.c is distinct from s.c then result.c := r.c else result.c := NULL end if; if r.d is distinct from s.d then result.d := r.d else result.d := NULL end if; return next result; end if; s := r; end loop; return; end; $$ language plpgsql; select * from foo(); regards Pavel Stehule 2009/7/10 Marcin Krawczyk : > Hi list, > > I was wondering if it was possible for a field in SQL query to return NULL > if latter value is exactly the same ? - for given ORDER BY clause, I guess. > For example, query returns: > > xxyy 1 4 true > xxyy 5 7 true > xxyy 21 8 true > yyzz 5 1 false > yyzz 7 7 false > yyzz 8 34 false > > I'd like the output to be: > > xxyy 1 4 true > NULL 5 7 NULL > NULL 21 8 NULL > yyzz 5 1 false > NULL 7 7 NULL > NULL 8 34 NULL > > Is there any magical trick to achieve this ? > > regards > mk > -- 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] How update a table within a join efficiently ?
2009/7/10 Andreas : > Hi, > how would I update a table within a join in a more efficient way? > > E.g. the folowing case: > table_a holds abstract elements. One column represents "priority" which can > be based on information of other tables. > table_b might hold such details in a column "size" for about 3000 of 8 > records out of table_a. > > I'd like to do this: > UPDATE table_a > SET table_a.prio = CASE WHEN size >= 10 THEN 1 ELSE 2 END > FROM table_a JOIN table_b USING (table_a_id) hello don't repeat target table in FROM clause UPDATE table_a SET table_a.prio = CASE WHEN size >= 10 THEN 1 ELSE 2 END FROM table_b WHERE table_a.table_a_id = table_b.table_a_id; regards Pavel Stehule > > This doesn't work. > But the folowing does, though it looks not efficient with those 3000 SELECTs > instead of one preparing JOIN that fetches the relevant info. :( > > UPDATE table_a > SET prio = > ( > SELECT CASE WHEN size >= 10 THEN 1 ELSE 2 END > FROM table_b > WHERE table_a.table_a_id = table_b.table_a_id > ) > WHERE table_a_id IN (SELECT table_a_id FROM table_b); > > Is there a better way? > > -- > 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] Updating a specific number of rows in pl/pgsql
2009/8/11 D'Arcy J.M. Cain : > On Mon, 10 Aug 2009 17:52:36 -0700 > "Peter Headland" wrote: >> I can get the rows I want to update like this: >> >> SELECT * >> FROM queue >> WHERE id = p_queue_id >> ORDER BY rank >> LIMIT p_number_of_items; >> >> Of course, there may not be p_number_of_items available in the queue. >> >> I want to update all the rows in the cursor in the same way: >> >> UPDATE queue SET assigned = TRUE; > > Assuming that there is a unique identifier on queue, let's call it > queue_id, you should be able to do something like this: > > UPDATE queue SET assigned = TRUE > WHERE queue_id IN (SELECT queue_id > FROM queue > WHERE id = p_queue_id > ORDER BY rank > LIMIT p_number_of_items); > there are one fast trick http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing p.s. replace DELETE by UPDATE regards Pavel Stehule > -- > D'Arcy J.M. Cain | Democracy is three wolves > http://www.druid.net/darcy/ | and a sheep voting on > +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. > > -- > 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] Updating a specific number of rows in pl/pgsql
2009/8/11 Peter Headland : >> there are one fast trick >> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing > > Thanks - that's a very useful page! > > Unfortunately, there is no single column that provides a unique id, and I am > reluctant to add one (for example, using a sequence and a new index) for > performance reasons. ctid is unique system column in every table. postgres=# create table x(a int); CREATE TABLE Time: 655,062 ms postgres=# insert into x values(10); INSERT 0 1 Time: 49,237 ms postgres=# insert into x values(10); INSERT 0 1 Time: 1,740 ms postgres=# select ctid, a from x; ctid | a ---+ (0,1) | 10 (0,2) | 10 (2 rows) > > Given that additional constraint, is my original plan using a loop to iterate > over a cursor reasonable? I don't anticipate p_number_of_items being more > than 20. why not? for small number of iteration is loop over cursor good solution. Pavel Stehule > > -- > Peter Headland > Architect > Actuate Corporation > > > -Original Message- > From: Pavel Stehule [mailto:pavel.steh...@gmail.com] > Sent: Tuesday, August 11, 2009 03:55 > To: D'Arcy J.M. Cain > Cc: Peter Headland; pgsql-sql@postgresql.org > Subject: Re: [SQL] Updating a specific number of rows in pl/pgsql > > 2009/8/11 D'Arcy J.M. Cain : >> On Mon, 10 Aug 2009 17:52:36 -0700 >> "Peter Headland" wrote: >>> I can get the rows I want to update like this: >>> >>> SELECT * >>> FROM queue >>> WHERE id = p_queue_id >>> ORDER BY rank >>> LIMIT p_number_of_items; >>> >>> Of course, there may not be p_number_of_items available in the queue. >>> >>> I want to update all the rows in the cursor in the same way: >>> >>> UPDATE queue SET assigned = TRUE; >> >> Assuming that there is a unique identifier on queue, let's call it >> queue_id, you should be able to do something like this: >> >> UPDATE queue SET assigned = TRUE >> WHERE queue_id IN (SELECT queue_id >> FROM queue >> WHERE id = p_queue_id >> ORDER BY rank >> LIMIT p_number_of_items); >> > > there are one fast trick > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_first_n_rows_removing > > p.s. replace DELETE by UPDATE > regards > Pavel Stehule > >> -- >> D'Arcy J.M. Cain | Democracy is three wolves >> http://www.druid.net/darcy/ | and a sheep voting on >> +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. >> >> -- >> 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] Selecting values from comma separated string
Hello
postgres=# select * from foo;
+---+---+
| i | a |
+---+---+
| 1 | a |
| 2 | b |
| 3 | c |
+---+---+
(3 rows)
Time: 0,654 ms
postgres=# select * from foo where i = ANY (string_to_array('1,3',',')::int[]);
+---+---+
| i | a |
+---+---+
| 1 | a |
| 3 | c |
+---+---+
(2 rows)
Time: 0,914 ms
regards
Pavel Stehule
2009/8/26 Nacef LABIDI :
> Hi all,
>
> I want to write a function that takes as param a comma separated values
> string and perform a select matching these values.
>
> Here is the string '1,3,7,8'
>
> And I wan to perform a : SELECT * FROM my_table WHERE id IN (1, 3, 7, 8);
>
> Does anyone have a clue ?
>
> Thanks
>
> Nacef
>
--
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] Selecting values from comma separated string
2009/8/26 A. Kretschmer :
> In response to Nacef LABIDI :
>> Hi all,
>>
>> I want to write a function that takes as param a comma separated values
>> string
>> and perform a select matching these values.
>>
>> Here is the string '1,3,7,8'
>>
>> And I wan to perform a : SELECT * FROM my_table WHERE id IN (1, 3, 7, 8);
>
> Use EXECUTE sql_string, see
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
>
> For instance, simple example, untested:
>
Hello
> create function foo (my_string) returns setof record as $$
> declare
> sql text;
> begin
> sql:='SELECT * FROM my_table WHERE id IN (' || $1 || ')';
> return query execute sql;
> end;
>
It's dangerous solution - there can be sql injection attack
regards
Pavel Stehule
> The variable sql contains the whole query, and then execute that.
>
> HTH, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> 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] Selecting values from comma separated string
2009/8/26 Nacef LABIDI :
> Here I come again to ask how can I pass an array of values to a pgsql
> function when I call this function from a delphi program for example.
>
the driver have to support it. But why?
simply you can use varchar and string_to_array function.
Pavel
> Nacef
>
>
>
> On Wed, Aug 26, 2009 at 3:05 PM, Tom Lane wrote:
>>
>> "A. Kretschmer" writes:
>> > In response to Nacef LABIDI :
>> >> I want to write a function that takes as param a comma separated values
>> >> string
>> >> and perform a select matching these values.
>>
>> > Use EXECUTE sql_string,
>>
>> Safer to use string_to_array, for instance
>>
>> ... WHERE id = ANY(string_to_array('1,3,7,8', ',')::int[]) ...
>>
>> Of course this just begs the question of why the OP doesn't use an
>> array in the first place.
>>
>> 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
>
>
>
> --
> Nacef LABIDI
> nace...@gmail.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] NEED HELP COPY TO DYNAMIC OUTPUT FILE
Hello COPY in plpgsql are not allowed. regards Pavel Stehule 2009/8/30 Yogi Rizkiadi : > Hi admin, i'm gie from indonesia > > i wanna ask you how to make a dynamic output file from command COPY TO ? > > i have tried this : > > BEGIN > i:=0; > j:=10; > WHILE i < j LOOP > COPY (SELECT * FROM country) TO '/usr/proj/' || i || '.txt'; // need > attention here > END LOOP; > RETURN; > END > > but it seems getting wrong, so what the right syntax's ? > > Sorry for my bad english and thanks in advance > > > > Dapatkan alamat Email baru Anda! > Dapatkan nama yang selalu Anda inginkan sebelum diambil orang lain! -- 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] NEED HELP COPY TO DYNAMIC OUTPUT FILE
2009/8/30 Tom Lane : > Pavel Stehule writes: >> COPY in plpgsql are not allowed. > > I think it will work if you use an EXECUTE. > > regards, tom lane > I didn't test it. regards Pavel Stehule -- 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] PostgreSQL Function: how to know the number of the returned results of the Query
Hello look on PERFORM and GET DIAGNOSTICS statements http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS regards Pavel Stehule 2009/9/1 bilal ghayyad : > Hi List; > > I am building a function in the SQL (PostgreSQL), so I will be able to call > this function using a SELECT query. Inside the body of this function, I was > need to do the following but not able to know how: > > I have a SELECT statement (inside the function script itself), and I need to > know the number of the returned rows of that select query, if it is one row > or 2 or 3 , ... How? In which paramter I will be able to check this? > > For example, I have the following function: > > CREATE OR REPLACE FUNCTION get_credit_time(text, text) > RETURNS integer AS > $BODY$ > DECLARE > BEGIN > rate numberic(9,4); > SELECT rate from voiptariff where id= 9; > IF num_rows ==1 THEN -- As example, but I am asking how to do it? > . > ELSE > . > END IF > END > $BODY$ > LANGUAGE 'sql' IMMUTABLE > COST 100; > ALTER FUNCTION get_bool(text) OWNER TO gkradius; > > In this function, I need to check the number of returned rows of the > statement: SELECT rate from voiptariff where id= 9; because based on it I am > going to build if statment, How? > > Any help? > > Regards > Bilal > > > > > -- > 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] selecting latest record
Hello 2009/9/22 Louis-David Mitterrand : > Hi, > > I have a simple table > > price(id_product, price, date) > > which records price changes for each id_product. Each time a price > changes a new tuple is created. > > What is the best way to select only the latest price of each id_product? there are more ways - depends on what you wont. one way is SELECT * FROM price WHERE (id_product, date) = (SELECT id_product, max(date) FROM price GROUP BY id_product) Regards Pavel Stehule > > Thanks, > > -- > 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] @@Error equivalent in Postgresql
2009/10/21 maboyz : > > Hi, > > I am in the process of migrating our database from MS Server 2000 to > Postgres. I have a bunch of stored procs which i have to modify the syntax > so they work in postgresql. My ? is is there an equivalent for the @@Error > function in T-SQL for postgres: The stored proc i am converting is: > > ALTER PROCEDURE [dbo].[AuditAccounts] > > �...@returnvalue int output > AS > > SET NOCOUNT ON > > select * from > AdminAccts full join AmAccts > on adm_acc_AccountNo = am_acc_AccountNo > where > adm_acc_AccountNo is null > or am_acc_AccountNo is null > > Set @ReturnValue = @@Error > > I have wriiten the postgres function as follows : > > CREATE TYPE AuditAccount AS (adm_acc_AccountNo character varying, > am_acc_AccountNo character varying); > CREATE FUNCTION dint_AuditAccounts( ) > RETURNS SETOF AuditAccount AS > $BODY$ > BEGIN > RETURN QUERY > select * from "AdminAccounts" > full join "AmAccounts" > on "adm_acc_AccountNo" = "am_acc_AccountNo" > where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; > > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100 > ROWS 10; > > How do i implement exception handling in this case, if i want the function > to report back successful execution or failure just like the @@Error > function does in T-SQL? > -- Hello PostgreSQL has different model of error processing than MSSQL. When any exception is raised, then simply is raised and not silently ignored like in T-SQL. You can catch exception. See http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Then you can use SQLSTATE and SQLERRM variables. p.s. For similar function like your function use sql language. It could be more effective: CREATE FUNCTION dint_AuditAccounts(OUT adm_acc_AccountNo character varying, OUT am_acc_AccountNo character varying) RETURNS SETOF record AS $BODY$ select * from "AdminAccounts" full join "AmAccounts" on "adm_acc_AccountNo" = "am_acc_AccountNo" where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; $BODY$ LANGUAGE sql; You don't need set flags because planner see inside sql functions. Regards Pavel Stehule > View this message in context: > http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25995788.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] @@Error equivalent in Postgresql
2009/10/21 maboyz : > > Thanks for the response Pavel. So does this mean i will have to make the > @ReturnValue an OUT parameter too??? am a bit confused here, i guess what i > am driving at is, i see where you are going with the altered function you > suggeted but its fitting the exception handling into the grand scheme of > things so i can be able to manipulate it in the code just like you wd use > the @returnValue = @@Error. Thanks yes, if you like to return state, then you have to mark it as OUT. It's better if you are drop your knowledge from T-SQL and start from zero. PL/pgSQL is modern language based on Ada language. Mainly - it is too different than T-SQL stored procedures programming - but is near to Oracle's programming. It is exception based. So the programming based on returning state codes is very obsolete, and little bit difficult. You can emulate, but any protected block creates inner transaction and this should negative effect on speed - and it are some lines more. http://www.postgres.cz/index.php/PL/pgSQL_%28en%29 Pavel > > Pavel Stehule wrote: >> >> 2009/10/21 maboyz : >>> >>> Hi, >>> >>> I am in the process of migrating our database from MS Server 2000 to >>> Postgres. I have a bunch of stored procs which i have to modify the >>> syntax >>> so they work in postgresql. My ? is is there an equivalent for the >>> @@Error >>> function in T-SQL for postgres: The stored proc i am converting is: >>> >>> ALTER PROCEDURE [dbo].[AuditAccounts] >>> >>> �...@returnvalue int output >>> AS >>> >>> SET NOCOUNT ON >>> >>> select * from >>> AdminAccts full join AmAccts >>> on adm_acc_AccountNo = am_acc_AccountNo >>> where >>> adm_acc_AccountNo is null >>> or am_acc_AccountNo is null >>> >>> Set @ReturnValue = @@Error >>> >>> I have wriiten the postgres function as follows : >>> >>> CREATE TYPE AuditAccount AS (adm_acc_AccountNo character varying, >>> am_acc_AccountNo character varying); >>> CREATE FUNCTION dint_AuditAccounts( ) >>> RETURNS SETOF AuditAccount AS >>> $BODY$ >>> BEGIN >>> RETURN QUERY >>> select * from "AdminAccounts" >>> full join "AmAccounts" >>> on "adm_acc_AccountNo" = "am_acc_AccountNo" >>> where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; >>> >>> END; >>> $BODY$ >>> LANGUAGE 'plpgsql' VOLATILE >>> COST 100 >>> ROWS 10; >>> >>> How do i implement exception handling in this case, if i want the >>> function >>> to report back successful execution or failure just like the @@Error >>> function does in T-SQL? >>> -- >> >> Hello >> >> PostgreSQL has different model of error processing than MSSQL. When >> any exception is raised, then simply is raised and not silently >> ignored like in T-SQL. You can catch exception. See >> >> http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING >> >> Then you can use SQLSTATE and SQLERRM variables. >> >> p.s. For similar function like your function use sql language. It >> could be more effective: >> >> CREATE FUNCTION dint_AuditAccounts(OUT adm_acc_AccountNo character >> varying, >> OUT >> am_acc_AccountNo character varying) >> RETURNS SETOF record AS >> $BODY$ >> select * from "AdminAccounts" >> full join "AmAccounts" >> on "adm_acc_AccountNo" = "am_acc_AccountNo" >> where "adm_acc_AccountNo" is null or "am_acc_AccountNo" is null; >> $BODY$ >> LANGUAGE sql; >> >> You don't need set flags because planner see inside sql functions. >> >> Regards >> Pavel Stehule >> >>> View this message in context: >>> http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25995788.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 >> >> > > -- > View this message in context: > http://www.nabble.com/%40%40Error-equivalent-in-Postgresql-tp25995788p25998338.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] Table Valued Parameters
Hello 2009/10/23 Andrew Hall : > Hi, > > I was wondering whether anybody would be able to advise me on how (if it is > possible) to port some functionality from Oracle? > > This is just an example - in Oracle, I am able to do the following > Use refcursor, please. http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html Regards Pavel Stehule > -- > -- Create a data type which replicates the data structure of a single user > in my application. > -- I know that this can be done using PostgreSQL. > -- > > CREATE TYPE TY_APP_USER AS OBJECT > ( > aur_id INT > , aur_username VARCHAR2(30 CHAR) > , aur_is_account_enabled VARCHAR2(1 CHAR) > , aur_created_date DATE > , aur_updated_date TIMESTAMP > ) > / > > -- > -- Create a data type which can store many instances of a single > 'TY_APP_USER' > -- [essentially this is a table valued data type]. An instance of this data > type can be > -- created and populated by the client application [a java based one in my > case]. > -- > -- I can't find any reference to something > -- similar to this using postgreSQL. > -- > > CREATE TYPE TTY_APP_USER AS TABLE OF TY_APP_USER > / > > -- > -- Next define a stored procedure which can accept an instance of a > TTY_APP_USER data > -- type, and treat that instance as a table, for example ... > -- > > CREATE OR REPLACE PROCEDURE prAddUsers > ( > p_in_users IN tty_app_user > ) > IS > BEGIN > > INSERT > INTO > users > ( > aur_id > , aur_username > , aur_is_account_enabled > , aur_created_by > , aur_created_date > ) > SELECT > aur_id > , aur_username > , aur_is_account_enabled > , aur_created_by > , aur_created_date > FROM > TABLE > ( > CAST > ( > p_in_users AS tty_app_user > ) > ); > > END prUpdateUsers; > > My motivation for doing this is to reduce network round trips, instead of > having 1 call per record to be sent to the db, I can have 1 call passing all > values which I wish to store in the database. > > Sending multiple records to the database as a result of a single form > submission is a requirement that arises frequently [the example is just > intended to demonstrate the principle!], and I would be grateful if anybody > could help me to arrive at an optimal solution. > > Cheers, > > Andrew. > > > > > > > > Download Messenger onto your mobile for free. Learn more. -- 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] Converting T-SQL to PostgreSQL
Hello 2009/11/5 maboyz : > > I am trying to re write the following stored proc as a postgresql > function..but am new to postgres and it is kind of getting really > confusing trying to learn and let alone understand the syntax: > > CREATE PROCEDURE [dbo].[GetAppAvailability] > �...@auditavailable bit output, > �...@billingavailable bit output, > �...@returnvalue int output > AS > SET NOCOUNT ON > > set @AuditAvailable = (select app_Status from AppAvailability where > app_Functionality = 'Audit') > set @BillingAvailable = (select app_Status from AppAvailability where > app_Functionality = 'Billing') > > Set @ReturnValue = @@Error > > I have this in postgres but obviously it is not correct: > > CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character > varying, OUT billingAvailable character varying) > AS > $BODY$ > set $1 = (select "app_Status" from "AppAvailability" where > "app_Functionality" = 'Audit'); > set $2 = (select "app_Status" from "AppAvailability" where > "app_Functionality" = 'Billing'); > $BODY$ > LANGUAGE 'sql' VOLATILE > COST 100; > You can use SQL functions, but SQL has not assign statement. So you have to use plpgsql language. http://www.postgresql.org/docs/8.4/interactive/plpgsql.html You code should be CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character varying, OUT billingAvailable character varying) AS $BODY$ $1 = (select "app_Status" from "AppAvailability" where "app_Functionality" = 'Audit'); $2 = (select "app_Status" from "AppAvailability" where "app_Functionality" = 'Billing'); $BODY$ LANGUAGE plpgsql VOLATILE Usually is better if you forgot on T-SQL and try to learn language again. there are some good advices: a) don't use camel notation for identifiers b) don't use case sensitive identifiers like "some" c) don't create "SELECT only" functions Why do you use function? use view: CREATE VIEW GetAppAvailability AS SELECT (SELECT "app_Status" FROM "AppAvailability" WHERE "app_Functionality" = 'Audit'), (SELECT "app_Status" FROM "AppAvailability" WHERE "app_Functionality" = 'Billing'); some link http://www.postgres.cz/index.php/PL/pgSQL_%28en%29 regards Pavel Stehule > > -- > View this message in context: > http://old.nabble.com/Converting-T-SQL-to-PostgreSQL-tp26221691p26221691.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] Converting T-SQL to PostgreSQL
2009/11/5 Pavel Stehule : > Hello > > 2009/11/5 maboyz : >> >> I am trying to re write the following stored proc as a postgresql >> function..but am new to postgres and it is kind of getting really >> confusing trying to learn and let alone understand the syntax: >> >> CREATE PROCEDURE [dbo].[GetAppAvailability] >> �...@auditavailable bit output, >> �...@billingavailable bit output, >> �...@returnvalue int output >> AS >> SET NOCOUNT ON >> >> set @AuditAvailable = (select app_Status from AppAvailability where >> app_Functionality = 'Audit') >> set @BillingAvailable = (select app_Status from AppAvailability where >> app_Functionality = 'Billing') >> >> Set @ReturnValue = @@Error >> >> I have this in postgres but obviously it is not correct: >> >> CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character >> varying, OUT billingAvailable character varying) >> AS >> $BODY$ >> set $1 = (select "app_Status" from "AppAvailability" where >> "app_Functionality" = 'Audit'); >> set $2 = (select "app_Status" from "AppAvailability" where >> "app_Functionality" = 'Billing'); >> $BODY$ >> LANGUAGE 'sql' VOLATILE >> COST 100; >> > > You can use SQL functions, but SQL has not assign statement. So you > have to use plpgsql language. > http://www.postgresql.org/docs/8.4/interactive/plpgsql.html > > You code should be > > CREATE OR REPLACE FUNCTION GetAppAvailability(OUT auditAvailable character > varying, OUT billingAvailable character varying) > AS > $BODY$ BEGIN > $1 = (select "app_Status" from "AppAvailability" where > "app_Functionality" = 'Audit'); > $2 = (select "app_Status" from "AppAvailability" where > "app_Functionality" = 'Billing'); END > $BODY$ > LANGUAGE plpgsql VOLATILE > sorry, I left out main block. plpgsql function have to have block etc BEGIN END; > Usually is better if you forgot on T-SQL and try to learn language again. > > there are some good advices: > > a) don't use camel notation for identifiers > b) don't use case sensitive identifiers like "some" > c) don't create "SELECT only" functions > > Why do you use function? > > use view: > CREATE VIEW GetAppAvailability AS > SELECT (SELECT "app_Status" > FROM "AppAvailability" > WHERE "app_Functionality" = 'Audit'), > (SELECT "app_Status" > FROM "AppAvailability" > WHERE "app_Functionality" = 'Billing'); > > > some link > http://www.postgres.cz/index.php/PL/pgSQL_%28en%29 > > regards > Pavel Stehule >> >> -- >> View this message in context: >> http://old.nabble.com/Converting-T-SQL-to-PostgreSQL-tp26221691p26221691.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] LIMIT BASED ON PERCENT
2009/11/18 Guillaume Lelarge : > Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : >> No, It doesn't. >> In my machine: >> >> First select >> ERROR: syntax error at end of input >> LINE 1: select * from rapadura.cliente limit 20% >> ^ >> Second one: >> ERROR: argument of LIMIT must not contain subqueries >> >> Postgres 8.3 >> > > It works for Lee because obviously he's working on a 8.4 server. You can use > subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For earlier > releases, there's no way to do this in a single query. > yes, and don't use 20%. select * from foo order by somecol limit (select (count(*)*0.2)::int from foo) Regards Pavel > > -- > Guillaume. > http://www.postgresqlfr.org > http://dalibo.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] LIMIT BASED ON PERCENT
2009/11/18 Another Trad : > But there is any way to do it? CREATE OR REPLACE twenty() RETURNS SETOF foo AS $$ DECLARE rows int; r record; BEGIN rows := (SELECT count(*) FROM foo); FOR r IN EXECUTE 'SELECT * FROM r ORDER BY some col LIMIT ' || (rows * 0.2)::int LOOP RETURN NEXT r; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; this code should to work in 8.3 regards Pavel Stehule > > 2009/11/18 Guillaume Lelarge >> >> Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : >> > No, It doesn't. >> > In my machine: >> > >> > First select >> > ERROR: syntax error at end of input >> > LINE 1: select * from rapadura.cliente limit 20% >> > ^ >> > Second one: >> > ERROR: argument of LIMIT must not contain subqueries >> > >> > Postgres 8.3 >> > >> >> It works for Lee because obviously he's working on a 8.4 server. You can >> use >> subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For >> earlier >> releases, there's no way to do this in a single query. >> >> >> -- >> Guillaume. >> http://www.postgresqlfr.org >> http://dalibo.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] LIMIT BASED ON PERCENT
2009/11/18 Lee Hachadoorian : > On Wed, Nov 18, 2009 at 2:30 PM, Pavel Stehule > wrote: >> yes, and don't use 20%. >> >> select * from foo order by somecol limit (select (count(*)*0.2)::int from >> foo) >> >> Regards >> Pavel > > Is this faster on a large table? Because (select (count(*)*20/100)) worked > fine. > this is +/- same - 20/100 is maybe about 0.01% faster - you don't need one float to query cast, but this operation is only one pqr query. The problem is two full scan of table. Pavel -- 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] Sqldf - error message
2009/11/20 Tom Lane :
> "Marvelde, Luc te" writes:
>> If I run this SQL query:
>
>>> sqldf("SELECT
>> + dbo_tbl_Terrein.RingCentraleNaam,
>> + dbo_tbl_Broedsels.BroedselID
>> + FROM ((dbo_tbl_BroedselLocatie
>> + INNER JOIN dbo_tbl_Broedsels ON dbo_tbl_BroedselLocatie.BroedselID =
>> dbo_tbl_Broedsels.BroedselID)
>> + INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID =
>> dbo_tbl_Nestkasten.NestkastID)
>> + INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer =
>> dbo_tbl_Terrein.TerreinNummer
>> + WHERE (((dbo_tbl_Terrein.RingCentraleNaam)='Oosterhout a/d Waal'));")
>
>> I get the following message:
>
>> Error in sqliteExecStatement(con, statement, bind.data) :
>> RS-DBI driver: (error in statement: no such column:
>> dbo_tbl_Broedsels.BroedselID)
>
> It looks to me like sqldf is unaware of the rules about identifier
> case-folding in Postgres. That column would come back named
> "broedselid", but it's probably looking for "BroedselID".
> Or possibly it's expecting the qualifier "dbo_tbl_Broedsels."
> to be included in the returned column name. Either way, you
> need to bug sqldf's authors to fix it.
>
is it Postgres? I see "Error in sqliteExecStatement"
regards
Pavel Stehule
> 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
>
--
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] Sqldf - error message
Hello
2009/11/20 Marvelde, Luc te :
> Hi All!
>
> I just discovered sqldf and im very enthousiastic, as I am a big fan of R
> and I often get frustrated using Access for datamanagement.
>
> I tried running some queries from Access in R, and it works very well.
> However, I have problems with some queries.
>
> If I run this SQL query:
>
>> sqldf("SELECT
> + dbo_tbl_Terrein.RingCentraleNaam,
> + dbo_tbl_Broedsels.BroedselID
> + FROM ((dbo_tbl_BroedselLocatie
> + INNER JOIN dbo_tbl_Broedsels ON dbo_tbl_BroedselLocatie.BroedselID =
> dbo_tbl_Broedsels.BroedselID)
> + INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID =
> dbo_tbl_Nestkasten.NestkastID)
> + INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer =
> dbo_tbl_Terrein.TerreinNummer
> + WHERE (((dbo_tbl_Terrein.RingCentraleNaam)='Oosterhout a/d Waal'));")
>
> I get the following message:
>
> Error in sqliteExecStatement(con, statement, bind.data) :
> RS-DBI driver: (error in statement: no such column:
> dbo_tbl_Broedsels.BroedselID)
>
> ( I also tried removing the 'dbo_tbl_' part of the dataframe names, but
> still the same problem occurred)
I thing, you have a problem with data. Probably some creating script
was broken or some similar. The message is clean. Your database has
not column BroedselID. Recheck your query, please. Im not sure - maybe
SQLite identifiers are case sensitive. Maybe are defined as case
sensitive.
Regards
Pavel Stehule
>
> Now ofcoarse I looked on google, because I was sure someone would have had
> the same problem.. And yes, I found this website...
>
> http://code.google.com/p/sqldf/
>
> They say:
> This happens when you try to use variable with a dot in it (as dots have
> special meaning to SQL) or if you try to use SQL92 reserved keywords.
> SQLite/RSQLite replaces dots with underscores and changes keywords words so
> that they can be used. The keywords in question can be found by entering the
> following at the R command line:
>
> .SQL92Keywords
>
> In this list of keywords, i cannot find any word that is close to the error…
> So, i was wondering if someone knows how to solve it. preferably i do not
> want to change all variable names, as these names come from the 'big main
> database' of the institute.
>
> The site also says that SQLite/RSQLite can fix the problem… but then when i
> look in their help, i cannot see how i can easily use those commands…
>
> Anyone?
>
> Kind regards,
>
> Luc te Marvelde
> New member :)
>
>
>
>
--
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] Sqldf - error message
2009/11/20 Pavel Stehule :
> Hello
>
> 2009/11/20 Marvelde, Luc te :
>> Hi All!
>>
>> I just discovered sqldf and im very enthousiastic, as I am a big fan of R
>> and I often get frustrated using Access for datamanagement.
>>
>> I tried running some queries from Access in R, and it works very well.
>> However, I have problems with some queries.
>>
>> If I run this SQL query:
>>
>>> sqldf("SELECT
>> + dbo_tbl_Terrein.RingCentraleNaam,
>> + dbo_tbl_Broedsels.BroedselID
>> + FROM ((dbo_tbl_BroedselLocatie
>> + INNER JOIN dbo_tbl_Broedsels ON dbo_tbl_BroedselLocatie.BroedselID =
>> dbo_tbl_Broedsels.BroedselID)
>> + INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID =
>> dbo_tbl_Nestkasten.NestkastID)
>> + INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer =
>> dbo_tbl_Terrein.TerreinNummer
>> + WHERE (((dbo_tbl_Terrein.RingCentraleNaam)='Oosterhout a/d Waal'));")
>>
p.s. Your style is strange. Don't use useless parentheses
SELECT dbo_tbl_Terrein.RingCentraleNaam, dbo_tbl_Broedsels.BroedselID
FROM dbo_tbl_BroedselLocatie
INNER JOIN
dbo_tbl_Broedsels
ON dbo_tbl_BroedselLocatie.BroedselID =
dbo_tbl_Broedsels.BroedselID
INNER JOIN dbo_tbl_Nestkasten
ON dbo_tbl_BroedselLocatie.NestkastID =
dbo_tbl_Nestkasten.NestkastID
INNER JOIN
dbo_tbl_Terrein
ON dbo_tbl_Nestkasten.TerreinNummer =dbo_tbl_Terrein.TerreinNummer
WHERE dbo_tbl_Terrein.RingCentraleNaam ='Oosterhout a/d Waal'
Query is same and little bit more readable.
>> I get the following message:
>>
>> Error in sqliteExecStatement(con, statement, bind.data) :
>> RS-DBI driver: (error in statement: no such column:
>> dbo_tbl_Broedsels.BroedselID)
>>
>> ( I also tried removing the 'dbo_tbl_' part of the dataframe names, but
>> still the same problem occurred)
>
> I thing, you have a problem with data. Probably some creating script
> was broken or some similar. The message is clean. Your database has
> not column BroedselID. Recheck your query, please. Im not sure - maybe
> SQLite identifiers are case sensitive. Maybe are defined as case
> sensitive.
>
> Regards
> Pavel Stehule
>
>>
>> Now ofcoarse I looked on google, because I was sure someone would have had
>> the same problem.. And yes, I found this website...
>>
>> http://code.google.com/p/sqldf/
>>
>> They say:
>> This happens when you try to use variable with a dot in it (as dots have
>> special meaning to SQL) or if you try to use SQL92 reserved keywords.
>> SQLite/RSQLite replaces dots with underscores and changes keywords words so
>> that they can be used. The keywords in question can be found by entering the
>> following at the R command line:
>>
>> .SQL92Keywords
>>
>> In this list of keywords, i cannot find any word that is close to the error…
>> So, i was wondering if someone knows how to solve it. preferably i do not
>> want to change all variable names, as these names come from the 'big main
>> database' of the institute.
>>
>> The site also says that SQLite/RSQLite can fix the problem… but then when i
>> look in their help, i cannot see how i can easily use those commands…
>>
>> Anyone?
>>
>> Kind regards,
>>
>> Luc te Marvelde
>> New member :)
>>
>>
>>
>>
>
--
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] Stalled post to pgsql-sql
hello postgres=# create table foo(a timestamp(0)); CREATE TABLE Time: 111,105 ms postgres=# insert into foo values(now()); INSERT 0 1 Time: 1,292 ms postgres=# select now(), a from foo; now | a ---+- 2009-12-09 11:33:22.746217+01 | 2009-12-09 11:33:12 (1 row) Time: 21,010 ms regards Pavel Stehule 2009/12/9 sergey kapustin : > Hello, > I need a type that will store date and time, but without seconds and > microseconds (like timestamp does). At least without microseconds. > I also need all the operators to work. > Is this possible with one of existing date/time types or i need to create my > own? > > Thank you! > -- 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] FIND_IN_SET
2009/12/11 Michael Eshom : > I am on the marketing team for a popular forum system, and am also the > primary PostgreSQL tester/bugfixer. Currently our forum system treats > MySQL's FIND_IN_SET() as a boolean (eg whether or not the specified value > was found in the given set), which is fine since MySQL will treat any > integer greater than 0 as boolean true and 0 as boolean false. I have > already managed to write a FIND_IN_SET() function for Postgres that behaves > as boolean. However, we would like to be able to use the true functionality > of this function (so it will return an integer instead of boolean). > > This is where I've run into a problem. The mysqlcompat package has a > FIND_IN_SET() in it, but it requires plpgsql, and I'd rather not require > something that a regular user can't install themselves, regardless of how > simple it is for the host to add it. > > I did find another version of FIND_IN_SET() on a blog with several other > MySQL-compatible functions, and while it uses regular SQL, it requires the > generate_subscripts() function which isn't available in Postgres 8.1 - the > latest version officially supported by CentOS. > > Is there a way to do this without requiring plpgsql or generate_subscripts? Hello you can define own generate_subscripts function CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text) RETURNS int AS $$ SELECT i FROM generate_series(string_to_array($2,','),1) g(i) WHERE (string_to_array($2, ','))[i] = $1 UNION ALL SELECT 0 LIMIT 1 $$ LANGUAGE sql STRICT; CREATE OR REPLACE generate_subscripts(anyarray, int) RETURNS SETOF int AS $$ SELECT generate_series(array_lower($1,$2), array_upper($1,$2)) $$ LANGUAGE sql; Regards Pavel Stehule > -- > Michael "Oldiesmann" Eshom > Christian Oldies Fan > Cincinnati, Ohio -- 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] short-cutting if sum()>constant
Hello I found one ugly trick. You can multiply lines and SUM > cons could be replaced limit clause: postgres=# select * from data; a --- 3 2 1 4 2 3 (6 rows) Then SELECT * FROM WHERE and stop when SUM(a) = n then postgres=# select generate_series(1,a) from data; generate_series - 1 2 3 1 2 1 1 2 3 4 1 2 1 2 3 So If I would to check if there are sum(a) >= 10 then I can use LIMIT 10. If query returns ten rows, then result is true, else result is false select a, (a = generate_series(1,a))::int from data limit 12; -- stop after sum(a) = 12 postgres=# select sum(x) from (select 1 as x,(a = generate_series(1,a))::int from data limit 12) s; sum - 12 -- 12 is eq 12, so test is successful (1 row) Regards Pavel Stehule 2009/12/22 Ivan Sergio Borgonovo : > Hi, > > I'd like to know if > > select sum(qty) from t where status=37; > > is > constant. > > qty is always >0. > > Is there a way to skip examining further rows and return a result > ASAP? > > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > 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] short-cutting if sum()>constant
2009/12/23 Ivan Sergio Borgonovo : > On Wed, 23 Dec 2009 01:09:40 +0100 > Ivan Sergio Borgonovo wrote: > >> On Wed, 23 Dec 2009 00:00:31 +0100 >> Ivan Sergio Borgonovo wrote: >> >> > On Tue, 22 Dec 2009 20:47:18 +0100 >> > Pavel Stehule wrote: >> > >> > > Hello >> > > >> > > I found one ugly trick. You can multiply lines and SUM > cons >> > > could be replaced limit clause: >> > >> > The trick is really smart (and fun), kudos, really, it's always a >> > pleasure to read your solutions, thanks. >> > >> > But as expected: >> >> as unexpected... > > As even more unexpected... when all row are >0 and most of them are > equal to 1 the generate_series performs appreciably better (roughly > 15% faster). > And I think your version can be further optimised: > select count(*) from (select (generate_series(1,a))::int from > data limit 9000) s; > This perform 30% faster. > > So what's so slow in the plpgsql version? don't forget - plpgsql is interpret - it is best as glue for SQL statement. I don't thing so plpgsql is slow - speed is similar to using buildin functionality. But I am sure, rewritening your function to C could help. If you need maximal speed. I thing, so there are other trick, I am not sure if it is faster. You can create own aggregate. In state function you can calculate and check state value. If it is over your limit, then you can raise exception. So if your query will be finished with custom exception, then sum(c) > n is true. Regards Pavel Stehule > > Fortunately as expected when "enough" rows are >1 the for loop > solution perform much better. > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > 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] short-cutting if sum()>constant
2009/12/23 Ivan Sergio Borgonovo :
> On Wed, 23 Dec 2009 08:38:52 +0100
> Pavel Stehule wrote:
>
>> > As even more unexpected... when all row are >0 and most of them
>> > are equal to 1 the generate_series performs appreciably better
>> > (roughly 15% faster).
>> > And I think your version can be further optimised:
>> > select count(*) from (select (generate_series(1,a))::int from
>> > data limit 9000) s;
>> > This perform 30% faster.
>> >
>> > So what's so slow in the plpgsql version?
>>
>> don't forget - plpgsql is interpret - it is best as glue for SQL
>
> Yeah but how many times is it interpreted in a 1M cycle expecially
> when you ran it more than one time and the interpreter should be
> "hot"? I don't know how much "interpreter" work can be saved from a
> previous run of the function... but still there should be something
> left [1].
> plpgsql isn't duck typed and that function doesn't leave too much
> space for "interpretation".
> Unless I'm missing something, in a typed language like plpgsql that
> function could be easily turned into its C equivalent by the
> interpreter.
> I really thought the cost of running plpgsql was much lower in such
> kind of situation.
The problem is massive cycle. Plpgsql really isn't language for this.
This interpret missing own arithmetic unit. So every expression is
translated to SELECT statement
IF a > c ~ SELECT a > c
a = a + 1 ~ SELECT a + 1
these queries are executed in some special mode, but still it is more
expensive than C a = a + 1
>
> Consider that the cost doesn't come from the initial cost of
> interpreting the function but it is proportional to the numbers of
> cycles.
>
> Even hard coding the LIMIT in the plpgsql version doesn't make it
> faster enough. So it is not the planner.
>
> In a 10M rows dataset where 8332885 are =1 and the rest are =2:
>
> If I'm testing for >90
> I get 940.580 ms vs 1302.162 ms
> If I'm testing for >900
> I get 8816.263 ms vs 12306.483 ms
>
> BTW
> select sum(a) from data; takes 1999.492 ms.
> select count(*) from data; takes 1612.039 ms
>
it is slower, because there is one NULL test more.
> While smart your way is definitively more convoluted and should
> require much more cpu cycles and memory.
>
> The plperl version:
> create or replace function totano(int)
> returns int as
> $$
> my $b = 0;
> my $row;
> my $sth = spi_query("select a from data");
> while(defined($row=spi_fetchrow($sth))) {
> $b += $row->{a};
> if($b>=$_[0]) {
> return $b;
> }
> }
> return $b;
> $$ LANGUAGE plperl;
>
> Is 10 times slower than the plpgsql version.
>
PLpgSQL quietly uses cursors for FOR SELECT, your plperl uses normal
standard recordset - it means so your query generate full scan. In
your plperl code, you have to use explicit CURSORS. Then the speed
should be comparable. Plperlu is little bit faster, but plpgsql has
direct cursor support.
>> statement. I don't thing so plpgsql is slow - speed is similar to
>> using buildin functionality. But I am sure, rewritening your
>> function to C could help. If you need maximal speed.
>
> In my case it is not worth. I was just looking into something that
> didn't have to scan all the rows.
> With the real distribution of data and no real workload on the box
> your solution is faster but the difference while unexpectedly
> appreciable is nearly negligible.
>
> Anyway I'm not able to justify the difference in speed between
> plpgsql and your solution in such case unless plpgsql is switching
> back and forward between binary data and their text representation.
> It would be nice if someone that know plpgsql internals explain
> where the cost comes from.
I thing so most expensive part in your plpgsql code is two expression.
Regards
Pavel
>
>> I thing, so there are other trick, I am not sure if it is faster.
>> You can create own aggregate. In state function you can calculate
>> and check state value. If it is over your limit, then you can raise
>> exception. So if your query will be finished with custom exception,
>> then sum(c) > n is true.
>
> I may test it later.
>
> thanks
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> 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] short-cutting if sum()>constant
2009/12/23 Ivan Sergio Borgonovo : > On Wed, 23 Dec 2009 12:52:38 +0100 > Pavel Stehule wrote: > >> The problem is massive cycle. Plpgsql really isn't language for >> this. This interpret missing own arithmetic unit. So every >> expression is translated to SELECT statement >> >> IF a > c ~ SELECT a > c >> a = a + 1 ~ SELECT a + 1 > >> these queries are executed in some special mode, but still it is >> more expensive than C a = a + 1 > > I didn't get it. > I'd expect that since plpgsql should shere SQL data types it could > map easily something like > > if (a>b) then > or > a := a +b > > and something like a for in query loop was going to be highly > optimised as well. > plpgsql should be the most tightly bounded language to the internals > of pg SQL. no there are not any internal optimisation. PostgreSQL can optimize only execution plan - not individual expressions. > >> > select sum(a) from data; takes 1999.492 ms. >> > select count(*) from data; takes 1612.039 ms > >> it is slower, because there is one NULL test more. > > That didn't came as a surprise. It was there for comparison. > >> PLpgSQL quietly uses cursors for FOR SELECT, your plperl uses > > I didn't notice. That was my first plperl function in my life. > Anyway that means that that test didn't say anything about > interpreter speed and duck typing. you don't compare equal things. > >> > Anyway I'm not able to justify the difference in speed between >> > plpgsql and your solution in such case unless plpgsql is >> > switching back and forward between binary data and their text >> > representation. It would be nice if someone that know plpgsql >> > internals explain where the cost comes from. > >> I thing so most expensive part in your plpgsql code is two >> expression. > one expression: if >> loop_var > parameter << second expression: loop_var + 1 > I didn't understand. > > What's the reason the interpreter can't translate the if and the b := > row._a + b; into something that very resemble compiled C? a) simplicity. PLpgSQL interpret is very simple. b) mostly types are little bit different behave than natural C types - domains are different, C types doesn't know NULL value, ... so if you like maximum speed, then you can use C language. It is relative simple, much simpler than C procedures in T-SQL or Oracle. > plpgsql is not doing anything different than: > select count(*) from (select case when a>3 then 1 else 2 end, a+a > from data limit 900) as f; no - it doesn't any intelligence - it doesn't join expression together. It does exactly it, what you write. see http://okbob.blogspot.com/2009/12/how-dont-write-plpgsql-functions.html > One of the advantages of using plpgsql (and one annoyance too) is > that variables are declared and plpgsql should know how to operate > on them with native C code. It little bit nonsense. On 99% plpgsql use SPI api and work with variables via query interface. PLpgSQL can do loop statement, if statement, but for all others use internal query based API. > > BTW the best performer considering all constraints and data > distribution seems to be the simplest one: > > select sum(s.a) from (select a from data where a>0 limit 900) s; > Time: 2620.677 ms > this query is little but different, than you original request, but it could work for you. Regard Pavel Stehule > We've no negative int... and they are int, so they have to be no > less than 1 if they are not 0. If I know there are no 0, the > simplest version become even faster. > > When I randomly pick up values in [1,3] plpgsql and generate_series > start to perform similarly but still the simplest version is leading. > > When the interval is in [1,6] the plpgsql becomes faster than the > generate_series but the simplest version is still leading. > > Just when the interval is in [1,10] the plpgsql version and the > simplest one becomes comparable. > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > 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] short-cutting if sum()>constant
2009/12/23 Ivan Sergio Borgonovo : > On Wed, 23 Dec 2009 14:35:27 +0100 > Pavel Stehule wrote: > > >> a) simplicity. PLpgSQL interpret is very simple. >> b) mostly types are little bit different behave than natural C >> types - domains are different, C types doesn't know NULL value, ... > > But well... there should be a mapping somewhere between SQL int and > C and it can be plainly reused. > > $b = 5 + "10"; > > is going to be far more problematic to execute... but I guess that's > exactly the problem. > > create or replace function tano(z int, out b int) returns int as > $$ > declare > x varchar(4) = '12'; > y int = 10; > begin > b := x + y; > return; > end; > $$ language plpgsql; > > select * from tano(1); > ERROR: operator does not exist: character varying + integer > LINE 1: SELECT $1 + $2 > ^ > > From what I can guess... the interpreter is sending SQL commands to > pg and waiting result back. So actually the "interpreter" isn't > actually aware of data types... it just relies on SQL to spot data > type mismatch. > That means... that everything is converted back and forward to > *text*. no - not everything. plpgsql hold values in PostgreSQL native types. But these types are not 100% equal to C types. Integer is +- equal to C int. Varchar is absolutly different then C string. > On the other side once a SQL command is interpreted it actually > knows what data types are and can pretty quickly sum int to int just > taking care of nulls and overflows that would be anyway problems for > any other language trying to sum "SQL ints". > > Even if it had to sum int and bigint the code could be optimised for > just that and the need of a cast should be known in advance before > every "loop". it could be, but it isn't. PLpgSQL is very simple - you have to know, so every function is "recompiled" everytime when function is called first time in session. So there are not time for full optimalisations like C languages. PLpgSQL do fast non optimalised execution - like Pascal. If you need well optimized code, then you have to use C language and external stored procedures. PLpgSQL is best as glue of SQL statements. Not for numeric calculation, complex string operations. Regards Pavel Stehule > > Did I get it? > > That's what you were trying to make me understand with: > >> It little bit nonsense. On 99% plpgsql use SPI api and work with >> variables via query interface. PLpgSQL can do loop statement, if >> statement, but for all others use internal query based API. > >> this query is little but different, than you original request, but >> it could work for you. > > Yep... making clear a is an int simplify the problem quite a lot. > But you couldn't use generate_series if a was not an int. > > thanks > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > 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] short-cutting if sum()>constant
2009/12/23 Ivan Sergio Borgonovo :
> On Wed, 23 Dec 2009 11:36:31 -0500
> Tom Lane wrote:
>
>> Craig Ringer writes:
>> > Pavel Stehule wrote:
>> >> these queries are executed in some special mode, but still it
>> >> is more expensive than C a = a + 1
>>
>> > ... and may have different rules, so you can't just write a
>> > simple "map expressions to C equivalents" arithmetic evaluator.
>
>> Yeah. As an example, overflow is supposed to be caught in "a + 1",
>> unlike what would happen in C.
>
>> In principle you could map some of the builtin operators into
>> inline code, but it would be a great deal of work and the results
>> would be un-portable.
>
> Tank you all for being so patient...
> I really miss how actually procedural languages works internally.
>
> doesn't pg routinely map between SQL and C?
generally yes, but not directly.
for example: operator + for type integer is wrapped by function int4pl
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/int.c?rev=1.86
After parsing and transformations, the expression is transformed to
sequence of call functions like int4pl. PostgreSQL doesn't contains C
compiler - so it cannot compile to target code.
>
> What is the difference between
>
> select a+a from data;
> and
> a := a + a;
> in a plpgsql function?
nothing,
you have to know so plpgsql doesn't see expression a+a; An content of
any expression is invisible for plpgsql parser. PLpgSQL doesn't
understand to expressions. PLpgSQL knows so somewhere have to be
expression, or so somewhere have to boolean expression, but own
expression is black box for plpgsql interpret.
>
> plpgsql knows that a are eg. int so it could just use the same C
> code that it uses when it has to sum a+a in sql.
PLpgSQL knows it. But this knowledge isn't enough. You have to have a
real compiler to machine code. But PostgreSQL hasn't real compiler -
it is only set of some specialised interprets. There are SQL
interpret, there are PLpgSQL interpret. Nothing is translated to
machine code.
>
> My guess since I don't even know what to look for to get an idea of
> the internal working of plpgsql is that the interpreter translate
> the code into SQL (sort of...), it sends it to the parser through
> SPI_execute/prepare etc... (so yeah maybe for the "data" it is not
> really sending "text" representation of data) but still... the
> "code" has to be further interpreted...
>
+/-
plpgsql uses cached plans. So SPI_prepare is called only when
expression is first time evaluated (for session).
> So something like:
> a := a + a;
> turns out to be:
> SPI_prepare("SELECT $1 + $2", 2, ...);
> and this is going to be called for every loop.
> while I thought the SQL engine and plpgsql interpreter were nearer
> so that the interpreter could push directly in the SQL engine the
> values of a.
>
> Am I getting nearer?
there are two steps:
if (cached_plan == NULL)
cached_plan = prepare("SELECT $1 + $2, info_about_types[])
result = exec(cached_plan, values[], nulls[], &isnull)
some_like_move_result_to_variable(address_of_a)
Pavel
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> 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] please help me on regular expression
2010/2/2 Tena Sakai : > Hi everybody, > > I need a bit of help on postgres reqular expression. > With a table of the following definition: > > Table "tsakai.pheno" > Column | Type | Modifiers > ---+---+--- > subjectid | integer | not null > height | character varying | not null > race | character varying | not null > blood | character varying | not null > > I want to catch entries in height column that includes a > decimal point. Here's my attempt: > > select subjectid, height > from tsakai.pheno > where height ~ '[:digit:]+.[:digit:]+'; > > Which returns 0 rows, but if I get rid of where clause, > I get rows like: > > subjectid | height > ---+ > 55379 | 70.5 > 55383 | 69 > 55395 | 70 > 56173 | 71 > 56177 | 65.5 > 56178 | 70 > . . > . . > > And when I escape that dot after first plus sign with a backslash, > like this: > where height ~ '[:digit:]+\.[:digit:]+'; > then I get complaint: > > WARNING: nonstandard use of escape in a string literal > LINE 3: where height ~ '[:digit:]+\.[:digit:]+'; > ^ > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. > > From there, it was a downward spiral descent... > you have to use a prefix 'E' - E'some string with \backslash' for your case the reg. expr could be postgres=# select '70.5' ~ e'\\d+\.\\d+'; ?column? -- t (1 row) http://www.postgresql.org/docs/8.1/static/functions-matching.html or postgres=# select '70.5' ~ e'[[:digit:]]+\.[[:digit:]]+'; ?column? -- t (1 row) Regards Pavel Stehule > Please help. > > Thank you. > > Regards, > > Tena Sakai > tsa...@gallo.ucsf.edu > > > > -- > 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] very frustrating feature-bug
2010/2/17 silly sad : > On 02/17/10 13:51, Jasen Betts wrote: >> >> On 2010-02-17, silly sad wrote: >>> >>> acc=> >>> >>> CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT) >>> RETURNS usr AS $$ >>> INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4) >>> RETURNING usr.*; >>> $$ LANGUAGE sql SECURITY DEFINER; >>> >>> acc=> >>> >>> ERROR: return type mismatch in function declared to return usr >>> DETAIL: Function's final statement must be a SELECT. >>> CONTEXT: SQL function "add_user" >>> >>> SURPRISE :-) SURPRISE :-) >> >> SQL functions are inlined when invoked, and so must be valid subselects. >> >> rewrite it in plpgsql. > > thanx for advice. > > may i ask? when this feature will be fixed? > (now i am using 8.3.9) > please, report it as bug to pgsql-bugs Regards Pavel Stehule > > -- > 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] an aggregate to return max() - 1 value?
Hello select min(x) from (select x from data order by x desc limit 2) s; Pavel 2010/3/4 Louis-David Mitterrand : > Hi, > > With builtin aggregates is it possible to return the value just before > max(col)? > > Thanks, > > -- > 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] Private functions
Hello directly no. There is only workaround - you can verify inside in function content of pg_stat_activity_table - but this have to have a superuser rights. Regards Pavel Stehule 2010/3/13 Gianvito Pio : > Hi all, > is there a way to write a function that can only be called by another > function but not directly using SELECT function_name ( )? > Thanks > > -- > 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] Emacs sql-postgres (please, sorry for question not about PostgreSQL).
Hello try to look on http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Terminal.27s_configuration Regards Pavel Stehule 2010/3/18 Dmitriy Igrishin : > Hello all Emacs users! > > I am using Emacs recently. I love sql-mode, to use with PostgreSQL, > but I have problems with it. > When my SQL file (or buffer) are small (50-100 lines) I can send > it to SQLi buffer without any problems. But when I working with > large SQL file (e.g. complex database model, thousands of lines) > and send it to SQLi buffer it does not work properly. Something > going on and in SQLi buffer (psql) I see incomplete SQL > statement, for example: > super=# super'# super'# super'# super'# > It seems to single quote was not closed. But SQL is absolutely > correct and loads fine when I load it from file directly from psql. > I think, the problem with Emacs buffers... Please, help! > > And please sorry, for question not about PostgreSQL... > > Regards, > Dmitriy Igrishin -- 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] Emacs sql-postgres (please, sorry for question not about PostgreSQL).
2010/3/18 Tom Lane : > Dmitriy Igrishin writes: >> I am using Emacs recently. I love sql-mode, to use with PostgreSQL, >> but I have problems with it. >> When my SQL file (or buffer) are small (50-100 lines) I can send >> it to SQLi buffer without any problems. But when I working with >> large SQL file (e.g. complex database model, thousands of lines) >> and send it to SQLi buffer it does not work properly. Something >> going on and in SQLi buffer (psql) I see incomplete SQL >> statement, for example: >> super=# super'# super'# super'# super'# >> It seems to single quote was not closed. But SQL is absolutely >> correct and loads fine when I load it from file directly from psql. >> I think, the problem with Emacs buffers... Please, help! > > Might or might not be related, but I've seen psql lock up when I try to > paste more than a thousand or two characters into it via X11 > copy-and-paste. You might find that disabling libreadline (option -n > to psql) helps. > This behave is same without external copy/paste. SQL support in emacs is really great with some issues - like this or missing autocomplete. Pavel > 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 > -- 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] Does IMMUTABLE property propagate?
2010/3/25 Louis-David Mitterrand : > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: >> Petru Ghita writes: >> > "..immediately replaced with the function value" doesn't mean that the >> > results of a previously evaluated function for the same parameters are >> > stored and reused? >> >> No, it means what it says: the function is executed once and replaced >> with a constant representing the result value. > > So for example a function like: > > > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS > text > AS $$ > declare > outtext text; > begin > outtext = trim(regexp_replace(intext, E'\\s*Short( Break)?', > '', 'i')); > return outtext; > end; > $$ > LANGUAGE plpgsql; > yes it should be declared as immutable. plpgsql function is black box for executor, so you have to use some flag. language sql is different, executor see inside, so there you can not do it. Regards Pavel Stehule > could/should be declared immutable? > > Thanks, > > -- > 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] Does IMMUTABLE property propagate?
2010/3/25 Louis-David Mitterrand : > On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote: >> 2010/3/25 Louis-David Mitterrand : >> > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote: >> >> Petru Ghita writes: >> >> > "..immediately replaced with the function value" doesn't mean that the >> >> > results of a previously evaluated function for the same parameters are >> >> > stored and reused? >> >> >> >> No, it means what it says: the function is executed once and replaced >> >> with a constant representing the result value. >> > >> > So for example a function like: >> > >> > >> > CREATE OR REPLACE FUNCTION shorten_cruise_type(intext text) RETURNS >> > text >> > AS $$ >> > declare >> > outtext text; >> > begin >> > outtext = trim(regexp_replace(intext, E'\\s*Short( >> > Break)?', '', 'i')); >> > return outtext; >> > end; >> > $$ >> > LANGUAGE plpgsql; >> > >> >> yes it should be declared as immutable. plpgsql function is black box >> for executor, so you have to use some flag. language sql is different, >> executor see inside, so there you can not do it. > > Hmm, that's interesting. So for simple functions (like my example) it is > better to write them in plain sql? And in that case no 'immutable' flag > is necessary? > sure Pavel > -- > 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] Problem with function returning a result set
2010/4/8 Thomas Kellerer :
> Hi,
>
> I'm playing around with functions returning result sets, and I have a
> problem with the following function:
>
> -- Create sample data
> CREATE TABLE employee (id integer, first_name varchar(50), last_name
> varchar(50));
> INSERT INTO employee values (1, 'Arthur', 'Dent');
> INSERT INTO employee values (2, 'Zaphod', 'Beeblebrox');
> INSERT INTO employee values (3, 'Ford', 'Prefect');
> COMMIT;
>
> -- Create the function
> CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar)
> RETURNS TABLE(id integer, full_name text)
> AS
> $$
> BEGIN
>
> RETURN QUERY
> SELECT id, first_name||' '||last_name
> FROM employee
> WHERE last_name LIKE name_pattern ||'%';
> END
> $$
> LANGUAGE plpgsql;
>
> COMMIT;
>
> Now when I run:
>
> SELECT *
> FROM get_employees('D');
>
> I get one row returned which is correct, but the ID column is null (but
> should be 1). It does not depend which row(s) I select through the
> procedure. I also tried to change the datatype of the returned id to int8
> and an explicit cast in the SELECT statement, but to no avail.
>
> When I define the function using SQL as a language (with the approriate
> changes), the ID column is returned correctly.
>
> I'm using Postgres 8.4.3 on Windows XP
> postgres=> select version();
> version
> -
> PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit
> (1 row)
>
> What am I missing?
there are collision between SQL and PLpgSQL identifiers.
RETURNS TABLE(id integer, full_name text) AS
$$
BEGIN
RETURN QUERY
SELECT e.id, e.first_name||' '||e.last_name
FROM employee e
WHERE e.last_name LIKE e.name_pattern ||'%';
END
$$
LANGUAGE plpgsql;
use aliases.
Regards
Pavel Stehule
>
> Regards
> Thomas
>
>
> --
> 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] understanding select into
Hello 2010/4/9 John : > Hi, > I am reviewing a function written by some xTuple guys. What is interesting > about it is it uses the "INTO" statement like > > select something into _p from sometable where somecriteria. > > The function contiunes and uses the data retreived > _p.somefield_name > > And then the function ends. > > > Ok my question: > > I also thought the select "into" created a real table. But after running the > function the table does not exist. I see no where that a 'drop' is issued. > In fact the function uses lot's of select into's like (_test, _r, etc..). So > would some kind soul explain what is happening. > _p is record variable. See some lines before. There will be DECLARE part DECLARE p RECORD; There is plpgsql's SELECT INTO and SQL's SELECT INTO with little bit different syntax. First - target is list of variables or record variable, second - target is table. > Could it be that "_p" is drop automaticly when the function ends? Something > to do with scope. > _p is just variable regards Pavel Stehule > Could it have something to do with the fact the function returns only an > integer? And that causes the table to be drop. > > As you can see I'm lost here! > > > Johnf > > -- > 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] graphing time series data
2010/4/14 Louis-David Mitterrand :
> On Wed, Apr 14, 2010 at 08:46:13AM -0700, Richard Broersma wrote:
>> On Wed, Apr 14, 2010 at 7:54 AM, Louis-David Mitterrand
>> wrote:
>>
>> > Now, I'd like to make a graph of average prices per week, per
>> > id_product. As some prices don't vary much, distribution would not be
>> > ideal if I simply 'group by extract(week from p.modified)'.
>>
>> I created a view for a similar problem that I had. Only I was
>> calculating the counts per day. this query could be crafted to work
>> for you.
>>
>> CREATE OR REPLACE VIEW opendiscrepencydailycounts AS
>> WITH opendays(day) AS (
>> SELECT gs.day::date AS day
>> FROM generate_series((( SELECT
>> min(discrepencylist.discstartdt) AS min
>> FROM discrepencylist))::timestamp without time
>> zone, 'now'::text::date::timestamp without time zone, '1
>> day'::interval) gs(day)
>> )
>> SELECT opendays.day, ds.resolvingparty, count(opendays.day) AS
>> opendiscrepancies
>> FROM discrepencylist ds, opendays
>> WHERE opendays.day >= ds.discstartdt AND opendays.day <=
>> LEAST('now'::text::date, ds.resolutiondate)
>> GROUP BY opendays.day, ds.resolvingparty
>> ORDER BY opendays.day, ds.resolvingparty;
>
> You confirm my modus operandi. I tried the following which seems to give
> me optimal price distribution:
>
> select w.week,count( p.id_price) from
> (select generate_series(min(p.created_on),max(p.modified_on),'1
> week') as week from price p) as w join price p on
> (p.created_on <
> w.week + '7 days' and p.modified_on > w.week + '7 days') group
> by
> w.week order by w.week
>
> week | count
> +
> 2010-02-10 15:32:18+01 | 125369
> 2010-02-17 15:32:18+01 | 126882
> 2010-02-24 15:32:18+01 | 128307
> 2010-03-03 15:32:18+01 | 126742
> 2010-03-10 15:32:18+01 | 133596
> 2010-03-17 15:32:18+01 | 149019
> 2010-03-24 15:32:18+01 | 149908
> 2010-03-31 15:32:18+02 | 147617
>
> The rest should be easy from there!
I don't understand well. Why you don't use a function date_trunc(),
select date_trunc('week', created), count(*)
from price
group by date_trunc('week', created)
Regards
Pavel Stehuke
>
> Thanks for your input,
>
> --
> 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] graphing time series data
2010/4/14 Louis-David Mitterrand :
> On Wed, Apr 14, 2010 at 06:06:59PM +0200, Pavel Stehule wrote:
>> I don't understand well. Why you don't use a function date_trunc(),
>>
>> select date_trunc('week', created), count(*)
>> from price
>> group by date_trunc('week', created)
>
> Because if a price doesn't change for more than a week, then some weeks
> will have bad statistical distribution (i.e not including prices which
> only have their 'modified' updated). So I (think I) need to (1) generate
> the weeks separately and (2) average prices that are current for each
> week.
>
> But I could be missing something obvious.
ok
Pavel
>
>
> --
> 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] How to max() make null as biggest value?
Hello 2010/4/14 Feixiong Li : > Hi , guys , > > I am newbie for sql, I have a problem when using max() function, I need get > null when there are null in the value list, or return the largest value as > usual, who can do this? > max() returns max value of some column create table foo(a int); insert into foo values(10); insert into foo values(33); postgres=# select * from foo; a 10 33 (2 rows) Time: 0,524 ms postgres=# select max(a) from foo; max - 33 (1 row) there is function greatest postgres=# select greatest(1,2,34,2,1); greatest -- 34 (1 row) regards Pavel Stehule > i.e. max([1,2,3,4,5]) => 5 > max([1,2,3,4,5,null]) => null > > thanks in advance! > > Feixiong > feixion...@gmail.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] How to max() make null as biggest value?
2010/4/20 Pavel Stehule : > Hello > > 2010/4/14 Feixiong Li : >> Hi , guys , >> >> I am newbie for sql, I have a problem when using max() function, I need get >> null when there are null in the value list, or return the largest value as >> usual, who can do this? >> > > max() returns max value of some column > > create table foo(a int); > insert into foo values(10); > insert into foo values(33); > > postgres=# select * from foo; > a > > 10 > 33 > (2 rows) > > Time: 0,524 ms > postgres=# select max(a) from foo; > max > - > 33 > (1 row) > > there is function greatest > > postgres=# select greatest(1,2,34,2,1); > greatest > -- > 34 > (1 row) sorry, greates_with_null postgres=# create or replace function greatest_strict(variadic anyarray) returns anyelement as $$ select null from unnest($1) g(v) where v is null union all select max(v) from unnest($1) g(v) limit 1 $$ language sql;CREATE FUNCTION Time: 232.528 ms postgres=# select greatest_strict(1,6); greatest_strict - 6 (1 row) Time: 3.094 ms postgres=# select greatest_strict(1,6, null); greatest_strict - (1 row) but you need PostgreSQL 8.4 > > regards > Pavel Stehule > >> i.e. max([1,2,3,4,5]) => 5 >> max([1,2,3,4,5,null]) => null >> >> thanks in advance! >> >> Feixiong >> feixion...@gmail.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] best paging strategies for large datasets?
Hello 2010/5/12 Louis-David Mitterrand : > Hi, > > I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and > am in the process of developping a pager to let users leaf through it > (30K rows). > > Ideally I'd like to know when requesting any 'page' of data where I am > within the dataset: how many pages are available each way, etc. > > Of course that can be done by doing a count(*) query before requesting a > limit/offset subset. But the main query is already quite slow, so I'd > like to minimize them. look on scrollable cursors. see DECLARE CURSOR statement Regards Pavel Stehule > > But I am intrigued by window functions, especially the row_number() and > ntile(int) ones. > > Adding "row_number() over (order by )" to my query will > return the total number of rows in the first row, letting my deduce the > number of pages remaining, etc. row_number() apparently adds very little > cost to the main query. > > And ntile(buckets) seems nice too but I need the total row count for it > to contain a 'page' number: ntile(row_count/page_size). > > What better "paging" strategies are out there? > > Thanks, > > -- > 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] How to get CURRENT_DATE in a pl/pgSQL function
2010/5/18 Richard Broersma : > On Tue, May 18, 2010 at 12:08 PM, Kenneth Marshall wrote: > >> http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT >> >> you can use CURRENT_DATE. When I try to use it in >> the following pl/pgSQL function it gives the error: > >> BEGIN >> curtime := 'CURRENT_DATE'; >> LOOP > > > I'm not "up" on my pl/pgSQL, but isn't CURRENT_DATE a literal value so > it shouldn't to be enclosed in single quotes? no - it is mutable constant postgres=# CREATE OR REPLACE FUNCTION fo() RETURNS date AS $$ DECLARE d date; BEGIN d := CURRENT_DATE; RETURN d; END; $$ LANGUAGE plpgsql; CREATE FUNCTION Time: 450.665 ms postgres=# select fo(); fo 2010-05-18 (1 row) Regards Pavel Stehule > > Another idea would be to: CAST( now() AS DATE ) > > > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > > -- > 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] How to Insert and retrieve multilingual (Hindi "an Indian language") into PostgreSQL
Hello PostgreSQL doesn't support multilangual tables now - etc it isn't more than one collation per database. But you can store any langual text when this language is supported by UTF8. Just use UTF8 encoding for your database. Regards Pavel Stehule see help for initdb and createdb commands 2010/6/22 venkat : > Dear All, > > I want to insert and retrieve multilingual (Hindi) into > database.is PostgreSQL supports that ?if it is ... please guide me how to > enable multilingual in the table. > > I am waiting for your great response. > Thanks and Regards, > Venkat -- 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] Average of Array?
Hello 2010/6/25 Lee Hachadoorian : > Is there a function that returns the average of the elements of an > array? I'm thinking of something that would work like the avg() > aggregate function where it returns the average of all non-NULL > values. Can't find anything like it in the docs, and I'd like to make > sure I'm not missing something. it doesn't exists, but it is simple to develop it CREATE OR REPLACE FUNCTION array_avg(double precision[]) RETURNS double precision AS $$ SELECT avg(v) FROM unnest($1) g(v) $$ LANGUAGE sql; Regards Pavel Stehule > > Thanks, > > -- > Lee Hachadoorian > PhD Student, Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center > > -- > 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]
Hello
use a LO interface
http://www.postgresql.org/docs/8.4/static/lo-interfaces.html
exact form depends on language that you are use.
Regards
Pavel Stehule
2010/7/5 Trinath Somanchi :
> Hi,
>
> I'm new in using BLOB. How will the insert for storing very large byte
> strings into a column of data type Blob.
>
> On Mon, Jul 5, 2010 at 11:42 AM, silly sad wrote:
>>
>> On 07/05/10 09:57, wrote:
>>>
>>> Hi,
>>>
>>> How can I store Byte strings into a postgresql database.
>>> Is there any special command to store it. How will be the sql query.
>>
>> there is only '\0' byte incapable to input-output.
>> so u have to have it escaped at all costs _AND NOTHING MORE_.
>>
>> "escaped" doesn't mean "prefixed with backslash"
>> ("backslash method" cause a zero-byte to pass SQL parser an to be actually
>> stored, BUT
>> the output will be corrupted, because of this zero-byte will be actually
>> output)
>>
>> You may use the BYTEA type
>> (similar to the TEXT but with different input-output) which effectively
>> escapes zero-byte and a lot of other completely harmless bytes as well
>> (probably to reach a better overhead)
>>
>> Or you may introduce a pair of your own escape rules.
>>
>> Unfortunately there are no way to influence The Pg Developers to get rid
>> of the nasty god damned CSTRING off the input/output operations.
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> --
> Regards,
> --
> Trinath Somanchi,
>
--
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]
2010/7/5 silly sad : > On 07/05/10 10:30, Trinath Somanchi wrote: >> >> Hi, >> >> I'm new in using BLOB. How will the insert for storing very large byte >> strings into a column of data type Blob. > > i didn't advice you to use BLOB. > > you may store a string as long as 2GB at any TEXT or BYTEA field. you can do it, but don't do it! Escaping of large strings are not cheap, processing extra long SQL statements are extreme expensive on memory - so don't do it - or test it before and check memory and processor usage - and check it in testing environment with more than one user. The good size for text or bytea is less than 100M and real max isn't 2G but it is 1G. LO isn't these limits because it isn't accessable on SQL level. Regards Pavel Stehule > > -- > 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]
2010/7/5 silly sad : > On 07/05/10 10:43, Pavel Stehule wrote: > >> The good size for text or bytea is less than 100M and real max isn't >> 2G but it is 1G. LO isn't these limits because it isn't accessable on >> SQL level. > > any regular file on my filesystem isn't accessible on SQL level. > i am happy with them and never tried to store at a database. this is second extreme - you can use everything if you know what you do - and mainly it depends on applications and requests that you have to solve. Pavel > > -- > 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]
2010/7/5 silly sad : > On 07/05/10 11:03, Pavel Stehule wrote: >> >> 2010/7/5 silly sad: >>> >>> On 07/05/10 10:43, Pavel Stehule wrote: >>> >>>> The good size for text or bytea is less than 100M and real max isn't >>>> 2G but it is 1G. LO isn't these limits because it isn't accessable on >>>> SQL level. >>> >>> any regular file on my filesystem isn't accessible on SQL level. >>> i am happy with them and never tried to store at a database. >> >> this is second extreme - you can use everything if you know what you >> do - and mainly it depends on applications and requests that you have >> to solve. > > the trouble is the initiator of the thread didn't determine what is his > problem either storing of a zero-byte containing string or storing of huge > strings. > I answered him about BYTEA and he replied about BLOB. > I only tried to say i didn't say a word about BLOB. yes > > P.S. > Practically for storing pictures i prefer regular files. > how I say - it depends on application - sometime can be useful have to access to all data only from db connect - for million small pictures the bytea can be best. Pavel -- 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]
2010/7/5 silly sad : > On 07/05/10 11:18, Pavel Stehule wrote: > >>> P.S. >>> Practically for storing pictures i prefer regular files. >>> >> >> how I say - it depends on application - sometime can be useful have to >> access to all data only from db connect - for million small pictures >> the bytea can be best. > > i really love postgres TEXT type, but i hate CSTRING input-output why? and it isn't true - you can use a binary interface of PQexecParams - minimally for text and bytea it is very simple Pavel > bottleneck. > > -- > 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] How would I store little key-Nr to text lists?
2010/7/10 Andreas : > Hi > > how would I store little key-Nr to text lists? > Like e.g. > > colors > 1 red > 2 green > 3 blue > > Maybe I later also need to add 4 yellow? > > Obviously the numbers are used for foreign keys in data tables and the texts > appear in selects. > On the other hand users should chose from listboxes in an application so I > need to be able to read the (key, ext) tupels. > > For now I use 2 column tables that in selects get joined to a data table. > > Is there a more clever way ? you can use enums, you can use a hstore contrib module, but what you doing is best regards Pavel Stehule > > -- > 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] plpgsql out parameter with select into
Hello It cannot work, you mix the sql with plpgsql language 2010/8/17 Imre Horvath : > Hi! > > My question is, how can I get the out parameter from a function with > SELECT INTO by name? > I mean: > > create function testfunc1(OUT _status integer) returns integer as > $BODY$ > _status := 0; > $BODY$ > language plpgsql; > > create function testfunc2() as > declare > status integer; > $BODY$ > select into status * from testfunc1(); > $BODY$ > language plpgsql; > > create function testfunc3() as > declare > status integer; > $BODY$ > select into status _status from testfunc1(); > $BODY$ > language plpgsql; > > testfunc2 works, testfunc3 not. > > Thanks in advance: > Imre Horvath > > create or replace function test1(out _status integer) returns integer as $$ begin _status := 10; end; $$ language plpgsql; create or replace function test3() returns void as $$ declare status integer; begin select into status _status from test1(); raise notice '%', status; end; $$ language plpgsql; this working for me. postgres=# select test3(); NOTICE: 10 test3 ─── (1 row) Regards Pavel Stehule > -- > 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] Boolean output representation.
Hello
2010/8/26 Dmitriy Igrishin :
> Hey all,
>
> As is known, there are many valid literal values for the "true"
> and "false" state of boolean data type.
> True whether that output of boolean type shows only using
> the letters 't' and 'f' or its possible to change the output representation,
> e.g., to "true" or "false" ("1", "0")?
>
no, it isn't possible - you can write a simple formating function or
own custom data type.
Regards
Pavel Stehule
> Regards,
> Dmitriy
>
>
--
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] backup and document views and user functions
Hello 2010/8/30 David Harel : > Hi, > > I am looking for an easy way to backup views and functions. I want to store > them in our version control system. > move your functions and view to separate schema - and do backup with pg_dump -n schema regards Pavel Stehule > Using pgAdmin I can access them one at a time. I am looking for a better > reporting mechanism. psql shell command for such report will be just fine. > > Sorry for the lame question. I didn't find any clues on the web .(typically, > I fail to phrase the right keywords) > > -- > Thanks. > > David Harel, > > == > > Home office +972 77 7657645 > Cellular: +972 54 4534502 > Snail Mail: Amuka > D.N Merom Hagalil > 13802 > Israel > Email: harel...@ergolight-sw.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] backup and document views and user functions
hello 2010/8/30 Peter Steinheuser : > You'll probably have to write something (a function) that pulls the data out > of pg_catalog. > You can get a leg up on that by connecting to psql using -E, which echoes > hidden queries. > If you do a \df+ on a function, you'll see the query PG uses. > there is much more easy way to get a function source code SELECT pg_catalog.pg_get_functiondef(oid) Regards Pavel Stehule > ex. > production=# \df+ myschema.* > > * QUERY ** > SELECT n.nspname as "Schema", > p.proname as "Name", > pg_catalog.pg_get_function_result(p.oid) as "Result data type", > pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", > CASE > WHEN p.proisagg THEN 'agg' > WHEN p.proiswindow THEN 'window' > WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN > 'trigger' > ELSE 'normal' > END as "Type", > CASE > WHEN p.provolatile = 'i' THEN 'immutable' > WHEN p.provolatile = 's' THEN 'stable' > WHEN p.provolatile = 'v' THEN 'volatile' > END as "Volatility", > pg_catalog.pg_get_userbyid(p.proowner) as "Owner", > l.lanname as "Language", > p.prosrc as "Source code", > pg_catalog.obj_description(p.oid, 'pg_proc') as "Description" > FROM pg_catalog.pg_proc p > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace > LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang > WHERE n.nspname ~ '^(myschema)$' > ORDER BY 1, 2, 4; > ** > > > > On Mon, Aug 30, 2010 at 2:21 PM, David Harel wrote: >> >> Hi, >> >> I am looking for an easy way to backup views and functions. I want to >> store them in our version control system. >> >> Using pgAdmin I can access them one at a time. I am looking for a better >> reporting mechanism. psql shell command for such report will be just fine. >> >> Sorry for the lame question. I didn't find any clues on the web >> .(typically, I fail to phrase the right keywords) >> >> -- >> Thanks. >> >> David Harel, >> >> == >> >> Home office +972 77 7657645 >> Cellular: +972 54 4534502 >> Snail Mail: Amuka >> D.N Merom Hagalil >> 13802 >> Israel >> Email: harel...@ergolight-sw.com >> > > > > -- > Peter Steinheuser > psteinheu...@myyearbook.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] Is there a conditional string-concatenation ?
Hello
more simply
postgres=# CREATE OR REPLACE FUNCTION public.foo(a text, b text, c text)
RETURNS text
LANGUAGE sql
AS $function$
SELECT coalesce($1 || $2 || $3,
$1 || $2,
$2 || $3)
$function$
Regards
Pavel Stehule
2010/10/12 Osvaldo Kussama :
> 2010/10/12 Andreas :
>> Hi,
>> Is there a conditional string-concatenation ?
>>
>> I'd like to have an elegant way to connect 2 strings with some 3rd element
>> between only if there really are 2 strings to connect.
>>
>> e.g.
>> MyCat ( 'John', '_', 'Doe' ) --> 'John_Doe'
>> while
>> MyCat ( 'John', '_', '' ) --> 'John'
>> MyCat ( '', '_', 'Doe' ) --> 'Doe'
>> MyCat ( '', '_', '' ) --> NULL
>>
>> It should treat NULL and '' equally as empty
>> and it should trim each of the 3 elements.
>>
>> so
>> MyCat ( ' John ', '_', NULL ) --> 'John'
>> MyCat ( 'John', NULL, 'Doe' ) --> 'JohnDoe'
>>
>
>
> Try:
> bdteste=# SELECT nullif(ltrim(rtrim(coalesce(c1,'') || coalesce(c2,'')
> || coalesce(c3,''),' _'),' _'),'')
> bdteste-# FROM (VALUES ('John', '_', 'Doe'),('John', '_', ''),('',
> '_', 'Doe'),('', '_', ''),(' John ', '_', NULL),('John',
> NULL, 'Doe')) AS foo(c1,c2,c3);
> nullif
> --
> John_Doe
> John
> Doe
>
> John
> JohnDoe
> (6 rows)
>
> Osvaldo
>
> --
> 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] insert into table from list or array ?
Hello
2010/10/18 Andreas :
> Hi,
>
> is it possible to insert into a table from list or an array ?
>
yes, it's possible
INSERT INTO tmptab
SELECT v
FROM unnest(string_to_array('1,2,4,2,1',',')) g(v)
Regards
Pavel Stehule
> Suppose there is a set of numbers that might be IDs of tables within the DB.
> To work with them I'd need a temporary table that just holds a single column
> with those numbers.
> Something like
>
> create temporary table tmptable as select id from
> ( 2, 3, 5, 7, 11, 13, 17, 19, 23, 31,
> 37, ... );
>
> would be great.
>
> I get those numbers as textfile with 10 numbers per line and devided by
> comma+space as in the sample above, though the comma+space is negotiable if
> this were an issue.
>
> :)
>
> --
> 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] Trailing spaces - is there an easier way?
2010/11/4 Dean Gibson (DB Administrator) : > I'm sure this has been asked before, but I couldn't find it: > > I have a "zzz CHAR (8)" field. It needs to be CHAR because trailing spaces > need to be ignored for most operations. However, I need to concatenate it > with another (literal) string and need the spaces to be significant in that > operation. The ONLY WAY I could find to do it in v9.0.1 was (represented > in the following function): > > CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT > RETURNS NULL ON NULL INPUT > IMMUTABLE > LANGUAGE SQL AS $SQL$ > SELECT RPAD( $1, OCTET_LENGTH( $1 ) ) > $SQL$; > > And then of course I write: > > SELECT padded( zzz ) || '/' || ... > > Is there a better way? > > nic=# SELECT ''::char(6) || ''; ?column? -- (1 row) Time: 2.710 ms nic=# SELECT ''::char(6)::cstring || ''; ?column? (1 row) regards Pavel Stehule -- 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] Need help with plpgsql function.
2010/11/14 berelith :
>
> Hi,
>
> I'm creating the function on a postgres 8.2 server.
> I would like the function to accept half a dozen varied parameters (varchars
> and timestamps).
> The first parameter will determine which one of the 6 different select
> queries that function is going to run.
>
> The function will return all the rows from the chosen select statement.
>
> I've been reading the postgresql documentation in creating functions that
> returns rowset and I've read about plpgsql. It seems to be what I need
> because there is going to be conditional statements.
>
> I'm just having a hard time putting it all together, and I'm not sure about
> the syntax and how to return the selected rows back into OUT parameters.
>
> This is a short pseudo example:
>
> CREATE OR REPLACE FUNCTION report (
> -- function arguments, type will determine which one of the 6 queries to run
> IN type character varying(20),
> IN param1 character varying(255),
> IN param2 timestamp,
> -- returned values
> OUT code table.code%TYPE,
> OUT name table.name%TYPE
> )
> RETURNS SETOF rows
> { LANGUAGE PLPGSQL
> IF type like 'type A' THEN
> SELECT code, name INTO rows FROM tableA join some table ;
> return rows
> ELSIF type like 'type B' THEN
> SELECT code, name INTO rows FROM tableB join someothertable ... ;
> return rows
> ELSE
> RETURN VOID
> END IF;
> }
>
>
Hello
you can use a RETURN QUERY statement - some like
CREATE OR REPLACE FUNCTION foo(IN i int, OUT a int, OUT b int)
RETURNS SETOF RECORD AS $$
BEGIN
IF i = 1 THEN
RETURN QUERY SELECT 10,20 UNION ALL SELECT 30,40;
ELSE
RETURN QUERY SELECT 60,70 UNION ALL SELECT 80,90;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM foo(1);
SELECT * FROM foo(2);
Regards
Pavel Stehule
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Need-help-with-plpgsql-function-tp3264047p3264047.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] Need help with plpgsql function.
2010/11/14 Adrian Klaver : > On Saturday 13 November 2010 11:15:51 pm Pavel Stehule wrote: > >> > } >> >> Hello >> >> you can use a RETURN QUERY statement - some like >> >> CREATE OR REPLACE FUNCTION foo(IN i int, OUT a int, OUT b int) >> RETURNS SETOF RECORD AS $$ >> BEGIN >> IF i = 1 THEN >> RETURN QUERY SELECT 10,20 UNION ALL SELECT 30,40; >> ELSE >> RETURN QUERY SELECT 60,70 UNION ALL SELECT 80,90; >> END IF; >> RETURN; >> END; >> $$ LANGUAGE plpgsql; >> >> SELECT * FROM foo(1); >> SELECT * FROM foo(2); >> >> Regards >> >> Pavel Stehule >> > > FYI the OP is using 8.2 :) RETURN QUERY is 8.3+ sorry :) then RETURN QUERY query --> DECLARE r record; BEGIN FOR r IN SELECT RETURN NEXT r; END FOR; ... Regards Pavel Stehule > > -- > Adrian Klaver > adrian.kla...@gmail.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] error null value in column" concat_id" violates not-null constraint
Hello 2010/11/25 Ana Louro : > Hi, > I'm just beggining in PostgreSql 9.0 > > I've created a table ,like this: > > CREATE TABLE auxiliar > ( > ano integer, > codigodc character varying, > id character varying, > concat_id character varying NOT NULL, > CONSTRAINT concat PRIMARY KEY (concat_id); > > Now i want to insert values on concat_id resulting from a function > called "concat_id" > > INSERT INTO concat_id > SELECT(concat_id) FROM auxiliar ; insert has a syntax: INSERT INTO function call has a syntax funcname(parameters) so if I would to fill table auxiliar from some function, then I'll use a statement INSERT INTO auxilar SELECT * FROM funcname(..) Regards Pavel Stehule > > I get "error null value in column"concat_id" violatres not null > constraint > > > > Could anyone tell me what am i doing wrong? > > Ana > > > > > > -- > 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] conditional aggregates
Hello use a CASE statement http://www.postgresql.org/docs/7.4/static/functions-conditional.html Regards Pavel Stehule 2010/12/8 Marcin Krawczyk : > Hi list, > Can anyone advise me on creating an aggregate that would take additional > parameter as a condition ? For example, say I have a table like this > id;value > 1;45 > 2;13 > 3;0 > 4;90 > I'd like to do something like this > SELECT min_some_cond_aggregate(value,0) FROM table > to get the minimal value from table greater than 0, in this case 13. > I can't do SELECT min(value) FROM table WHERE value > 0 as this will mess my > other computations. My current solution involves a function operating on the > output of array_accum from the docs, but I'm looking for more elegant > solution. > Is this possible at all ? I'm running 8.1. > > regards > mk > -- 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] How to convert string to integer
Hello you can use a ::int for converting to integer. Or better - you can alter column to integer. It will be faster and more correct. Regards Pavel Stehule 2010/12/15 venkat : > Dear All, > How do i convert string to int > select SUM(pan_1) from customers1 where name='101' > When i run the above query i m getting "function sum(character varying) > does not exist".. > Please anyone can guide me.. > Thanks > > -- 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] data import
Hello 2010/12/21 Viktor Bojović : > Hi, > > can anyone recommend me a windows and linux free tools for importing data > into postgre. > Source files are CSV or excel. PostgreSQL can read a CVS files via a COPY statement. You can use a \copy metacommand too from psql Regards Pavel Stehule > Thanx in advance > -- > --- > Viktor Bojović > --- > Wherever I go, Murphy goes with me > -- 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] create function problem
Hello you badly use a IF statement. It's not C. Every IF must to finish with END IF this is IF .. THEN ELSEIF .. THEN .. ELSE END IF Regards Pavel Stehule 2010/12/30 Gary Stainburn : > Hi folks, > > I'm writing my first plpsql function in ages and I'm going blind trying to see > why it won't create. The error message and the code are below. I'm guessing > it's something blindingly obvious, but can someone please point it out to me. > > ta > > Gary > > The actual line number is the LANGUAGE line at the end of the file. > > goole=# \i project_delivery_date.sql > psql:project_delivery_date.sql:42: ERROR: syntax error at or near ";" > LINE 37: END; > ^ > goole=# \q > [r...@stan t-cards]# cat project_delivery_date.sql > -- vim: ft=sql et ai ic > -- > -- project_delivery_date() - project delivery date from existing dates > > CREATE FUNCTION project_delivery_date(date,date,date,date) RETURNS date AS $$ > DECLARE > eta_dealer ALIAS FOR $1; > eta_customer ALIAS FOR $2; > req_date ALIAS FOR $3; > act_date ALIAS FOR $4; > eta date; > > BEGIN > IF act_date IS NOT NULL THEN > return act_date; > END IF; > IF eta_dealer IS NOT NULL AND eta_customer IS NULL THEN > eta := eta_dealer; > ELSE IF eta_dealer IS NULL AND eta_customer IS NOT NULL THEN > eta := eta_customer; > ELSE IF eta_dealer IS NULL AND eta_customer IS NULL THEN > eta := NULL; > ELSE IF eta_dealer > eta_customer THEN > eta := eta_dealer; > ELSE > eta := eta_customer; > END IF; > IF eta IS NOT NULL AND req_date IS NULL THEN > RETURN eta; > END IF; > IF eta IS NULL AND req_date IS NOT NULL THEN > RETURN req_date; > END IF; > IF eta IS NULL AND req_date IS NULL THEN > RETURN NULL; > END IF; > IF eta > req_date THEN > RETURN eta; > END IF; > RETURN req_date; > END; > $$ LANGUAGE 'plpgsql'; > > [r...@stan t-cards]# > > -- > Gary Stainburn > I.T. Manager > Ringways Garages > http://www.ringways.co.uk > > -- > 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] aggregation of setof
Hello
use a array constructor instead
SELECT ARRAY(SELECT ...)
Regards
Pavel Stehule
2011/1/31 Andreas Gaab :
> Functions apparently cannot take setof arguments.
>
>
>
> Postgres 8.4:
>
>
>
> CREATE OR REPLACE FUNCTION testtable(IN setof anyarray)
>
> RETURNS anyarray AS
>
> $BODY$
>
> SELECT $1 LIMIT 1;
>
> $BODY$
>
> LANGUAGE 'sql' STABLE;
>
>
>
> à
>
> ERROR: functions cannot accept set arguments
>
>
>
>
>
>
>
> Von: Viktor Bojović [mailto:viktor.bojo...@gmail.com]
> Gesendet: Samstag, 29. Januar 2011 09:28
> An: Andreas Gaab
> Betreff: Re: [SQL] aggregation of setof
>
>
>
> i have never used that type but maybe you can try this;
>
> -create function which returns text[], and takse setof text as argument (if
> possible)
>
> -reach every text[] in set of text[] using array index
>
> -return values using "return next" for each text in text[] which is in set
> of text[]
>
>
>
> On Fri, Jan 28, 2011 at 12:42 PM, Andreas Gaab wrote:
>
> Hi all,
>
>
>
> I would like to write a query, which aggregates the results of
> regexp_matches(). The problem is that regexp_matches returnes setof text[]
> as documented even if I discard the global flag
> (http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
> ). Thus resulting in an error when I try to aggregate the result:
>
>
>
> “
>
> SELECT array_accum(
>
> regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)')
>
> )
>
> ---
>
> ERROR: set-valued function called in context that cannot accept a set
>
> ** Fehler **
>
> ERROR: set-valued function called in context that cannot accept a set
>
> SQL Status:0A000
>
> “
>
>
>
> Can I convert a ‚setof text[]‘ to a ‚text[]‘?
>
>
>
> Alternatively I could use a sub-select, but I am curious if there are other
> solutions around.
>
>
>
> Regards,
>
> Andreas
>
>
>
> ___
>
>
>
> SCANLAB AG
>
> Dr. Andreas Simon Gaab
>
> Entwicklung • R & D
>
>
>
> Siemensstr. 2a • 82178 Puchheim • Germany
>
> Tel. +49 (89) 800 746-513 • Fax +49 (89) 800 746-199
>
> mailto:a.g...@scanlab.de • www.scanlab.de
>
>
>
> Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351
>
> Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
>
> Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
>
> ___
>
>
>
>
> --
> ---
> Viktor Bojović
> ---
> Wherever I go, Murphy goes with me
--
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] using of select (myfunction()).* is so slow
Hello If you use a record expansion over function's result, then function is called once for record's field. so don't do it on slow functions. Regards Pavel 2011/2/3 Gerardo Herzig : > Hi all, im using a function of my own in a subquery, and when wonderig > about the slowliness of this one, y relalize that: > > test=# SELECT (_xxfunction(854,'711H',11,false)).* ; > (4 filas) > --Result DELETED > Duración: 1069,465 ms > > > glyms=# SELECT * from _xxfunction(854,'711H',11,false) ; > (4 filas) > Duración: 228,699 ms > > For privacy reasons, i just deleted the result and the function name, > but the resulst are obviously exactly the same, and the ().* form (which > i needed) is taking so much more...there is a reason why? A workaround? > > Thanks! > > Gerardo > > > -- > 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] how control update rows
Hello 2011/2/3 Sabin Coanda : > Hi there, > > I'd like to control the rows which are updated. I found useful the option > RETURNING in UPDATE syntaxt. Can I process the rows wich are returning there > ? > > I think to something like that: > > SELECT * > FROM ( > UPDATE "T" SET > "C" = 1 > WHERE "ID" > 100 > RETURNING * > ) x > It's not implemented yet. You can use a stored procedure or temp tables instead. Regards Pavel Stehule > TIA, > Sabin > > > > -- > 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] using of select (myfunction()).* is so slow
>> > > M ok Thanks...So there is no workaround/alternative to this? > yes, (SELECT x.* from func(...) x) instead SELECT (func(...)).* regards Pavel Stehule > Gerardo > > -- 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] UTF characters compromising data import.
Hello 2011/2/8 Gavin Beau Baumanis : > Hi Everyone, > > I am trying to import some data (provided to us from an external source) from > a CSV file using "\copy " > > But I get the following error message; > invalid byte sequence for encoding "UTF8": 0xfd > HINT: This error can also happen if the byte sequence does not match the > encoding expected by the server, which is controlled by "client_encoding". > > I understand the error message - but what I don't know is what I need to set > the encoding to - in order to import / use the data. > is impossible to import data without knowledge of encoding. you can use a some utils, that try to select a encoding http://linux.die.net/man/1/enca Regards Pavel Stehule > As always - thanks in advance for any help you might be able to provide. > > > Gavin "Beau" Baumanis > > -- > 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] "select c1, method(c2) group by c1" returns all values of c2 for c1
Hello you can use a string%agg function if you have a 9.0. On older version there is a array_agg function select c1, array_to_string(array_agg(c2),',') from T1 group by c1 regards Pavel Stehule 2011/2/8 Emi Lu : > Good afternoon, > > Is there a method to retrieve the following results: > > T1(c1 int, c2 varchar(128) ) > - > > > (1, val1); > (1, val2); > (1, val3); > (2, val1); > (3, val5); > (3, val6); > > select c1, method(c2) > group by c1 > > returns: > > 1, "val1, val2, val3" > 2, "val1" > 3, "val5, val6" > > > Thanks a lot! > > -- > Lu Ying > > -- > 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] "select c1, method(c2) group by c1" returns all values of c2 for c1
2011/2/8 Emi Lu : > On 02/08/2011 02:51 PM, Rolando Edwards wrote: >> >> SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where >> A.c1=B.c1),', ') from T1 A order by c1; >> >> Give it a Try !!! > > > Thanks a lot! Very helpful! > > array_to_string() + array() is exactly what I am looking for! > > I just wonder that array_to_string() + array() will provide me good > performance, right? If the calculation will be based on millions records. it depend on number of groups. This is correlated subquery - it must not be a best. Regards Pavel Stehule the best speed gives a string_agg, but it is only in 9.0 > > Thanks again! > -- > Lu Ying > > > > > >> Rolando A. Edwards >> MySQL DBA (SCMDBA) >> >> 155 Avenue of the Americas, Fifth Floor >> New York, NY 10013 >> 212-625-5307 (Work) >> 201-660-3221 (Cell) >> AIM& Skype : RolandoLogicWorx >> redwa...@logicworks.net >> http://www.linkedin.com/in/rolandoedwards >> >> >> -Original Message- >> From: pgsql-sql-ow...@postgresql.org >> [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu >> Sent: Tuesday, February 08, 2011 2:36 PM >> To: pgsql-sql@postgresql.org >> Subject: [SQL] "select c1, method(c2) group by c1" returns all values of >> c2 for c1 >> >> Good afternoon, >> >> Is there a method to retrieve the following results: >> >> T1(c1 int, c2 varchar(128) ) >> - >> >> >> (1, val1); >> (1, val2); >> (1, val3); >> (2, val1); >> (3, val5); >> (3, val6); >> >> select c1, method(c2) >> group by c1 >> >> returns: >> >> 1, "val1, val2, val3" >> 2, "val1" >> 3, "val5, val6" >> >> >> Thanks a lot! >> >> -- >> Lu Ying >> > > > -- > Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 > em...@encs.concordia.ca +1 514 848-2424 x5884 > > -- > 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] Determine length of numeric field
Hello probably you have to use a explicit cast postgres=# select length(10::numeric::text); length 2 (1 row) Regards Pavel Stehule 2011/2/15 Tony Capobianco : > I'm altering datatypes in several tables from numeric to integer. In > doing so, I get the following error: > > dw=# \d uniq_hits > Table "support.uniq_hits" > Column | Type | Modifiers > +-+--- > sourceid | numeric | > hitdate | date | > total | numeric | > hitdate_id | integer | > Indexes: > "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace > "support_idx" > Tablespace: "support" > > esave_dw=# alter table uniq_hits alter sourceid type int; > ERROR: integer out of range > > Sourceid should not be more than 5 digits long. I'm able to perform > this query on Oracle and would like something similar on postgres 8.4: > > delete from uniq_hits where sourceid in (select sourceid from uniq_hits > where length(sourceid) > 5); > > I haven't had much luck with the length or char_length functions on > postgres. > > Thanks. > Tony > > > -- > 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] PL/SQL block error
Hello you should to wrap code to function or inline function everywhere. psql doesn't support PL/SQL and doesn't support inlined PL/SQL blocks. Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hello, > I am very new to the postgres sql, i am trying to execute below pl/sql > block in postgres but i am getting error *'ERROR: syntax error at or near > "integer" '*, Could some one help me in this regard > > declare > v_count integer; > begin > select count(1) into v_count from pg_index inx where inx.indexrelid in > (select oid from pg_class where relname='action_pk' and relowner in > (select oid from pg_authid where rolname='postgres_ref')) > and inx.indrelid in > (select oid from pg_class where relname='action' and relowner in > (select oid from pg_authid where rolname='postgres_ref')); > > if v_count = 0 then > execute immediate 'create unique index action_pk > on action(acn_id)'; > end if; > end > > > > *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* > > Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, > Bangalore – 560037, India. > *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 > ; > > *Email:* sivannarayanre...@subexworld.com ; * > URL:* www.subexworld.com > > > > *Disclaimer: This e-mail is bound by the terms and conditions described at > **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> >
Re: [SQL] Oracle Equivalent queries in Postgres
Hello PostgreSQL uses a different system tables than Oracle. Try to use a standardized information_schema instead - these views are same on PostgreSQL and Oracle. http://www.postgresql.org/docs/current/static/information-schema.html Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hello, > I am checking the compatibility of my product with Postgres database and i > stucked in forming the below oracle equivalent queries in Postgres database, > Could some one help me pleaseee > > 1) Trying to get index and corresponding columns information of all the > tables in mentioned schema > > select inx.table_name as table_name, inx.index_name as > index_name,column_name, case ( when inx.index_type = 'IOT - TOP' then 'Y' > else 'N' end, > case > when inx.uniqueness = 'UNIQUE' then 'Y' > when inx.uniqueness = 'NONUNIQUE' then 'N' > end, > 'N' as ignore_dup_key, > cast(inc.column_position as NUMBER(10)) > fromall_indexes inx, > all_ind_columns inc > where inx.owner = '" + database.toUpperCase() + "' > > and inx.table_name = inc.table_name > and inx.index_name = inc.index_name > and inx.owner = inc.index_owner > and inx.owner = inc.table_owner > and inx.dropped = 'NO' > and inx.table_name = '" + tableName.toUpperCase() + "' > order by inx.table_name, inx.index_name, cast(inc.column_position as > NUMBER(10)) > > > 2) Trying to get the columns information of all the tables in mentioned > schema > > select tab.TABLE_NAME, > col.COLUMN_NAME, > col.DATA_TYPE, > cast(case when col.CHAR_COL_DECL_LENGTH is NULL then > col.DATA_PRECISION else col.CHAR_LENGTH end as NUMBER(10)), > cast(col.NULLABLE as CHAR(1)), > cast(col.COLUMN_ID as NUMBER(10)) > > fromall_tab_columnscol, > all_tables tab > where tab.TABLE_NAME= col.TABLE_NAME > and tab.OWNER = col.OWNER > and tab.OWNER = '" + database.toUpperCase() + "' > and tab.DROPPED = 'NO' > and tab.TABLE_NAME = '" + tableName.toUpperCase() + "' > order by tab.TABLE_NAME, cast(col.COLUMN_ID as NUMBER(10)) > > > > *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* > > Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, > Bangalore – 560037, India. > *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 > ; > > *Email:* sivannarayanre...@subexworld.com ; * > URL:* www.subexworld.com > > > > *Disclaimer: This e-mail is bound by the terms and conditions described at > **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> > <>
Re: [SQL] Oracle Equivalent queries in Postgres
hello list of tables http://www.postgresql.org/docs/current/static/infoschema-tables.html information about column http://www.postgresql.org/docs/current/static/infoschema-columns.html information about indexes - it's not part of ANSI/SQL so you have to look to pg_index or pg_indexes. Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hi Pavel, > In the given link, there are no views which can give information about > indexes. > > Is it possible for you to give me the equivalent queries in postgres? > > *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* > > Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, > Bangalore – 560037, India. > *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 > ; > > *Email:* sivannarayanre...@subexworld.com ; * > URL:* www.subexworld.com > > > > *Disclaimer: This e-mail is bound by the terms and conditions described at > **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> > > On 2/16/2011 4:20 PM, Pavel Stehule wrote: > > Hello > > PostgreSQL uses a different system tables than Oracle. Try to use a > standardized information_schema instead - these views are same on PostgreSQL > and Oracle. > > http://www.postgresql.org/docs/current/static/information-schema.html > > Regards > > Pavel Stehule > > > > > 2011/2/16 Sivannarayanreddy > >> Hello, >> I am checking the compatibility of my product with Postgres database and i >> stucked in forming the below oracle equivalent queries in Postgres database, >> Could some one help me pleaseee >> >> 1) Trying to get index and corresponding columns information of all the >> tables in mentioned schema >> >> select inx.table_name as table_name, inx.index_name as >> index_name,column_name, case ( when inx.index_type = 'IOT - TOP' then 'Y' >> else 'N' end, >> case >> when inx.uniqueness = 'UNIQUE' then 'Y' >> when inx.uniqueness = 'NONUNIQUE' then 'N' >> end, >> 'N' as ignore_dup_key, >> cast(inc.column_position as NUMBER(10)) >> fromall_indexes inx, >> all_ind_columns inc >> where inx.owner = '" + database.toUpperCase() + >> "' >> and inx.table_name = inc.table_name >> and inx.index_name = inc.index_name >> and inx.owner = inc.index_owner >> and inx.owner = inc.table_owner >> and inx.dropped = 'NO' >> and inx.table_name = '" + tableName.toUpperCase() + "' >> order by inx.table_name, inx.index_name, cast(inc.column_position as >> NUMBER(10)) >> >> >> 2) Trying to get the columns information of all the tables in mentioned >> schema >> >> select tab.TABLE_NAME, >> col.COLUMN_NAME, >> col.DATA_TYPE, >> cast(case when col.CHAR_COL_DECL_LENGTH is NULL then >> col.DATA_PRECISION else col.CHAR_LENGTH end as NUMBER(10)), >> cast(col.NULLABLE as CHAR(1)), >> cast(col.COLUMN_ID as NUMBER(10)) >> >> fromall_tab_columnscol, >> all_tables tab >> where tab.TABLE_NAME= col.TABLE_NAME >> and tab.OWNER = col.OWNER >> and tab.OWNER = '" + database.toUpperCase() + "' >> and tab.DROPPED = 'NO' >> and tab.TABLE_NAME = '" + tableName.toUpperCase() + "' >> order by tab.TABLE_NAME, cast(col.COLUMN_ID as NUMBER(10)) >> >> >> >> *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* >> >> Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, >> Bangalore – 560037, India. >> *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 >> ; >> >> *Email:* sivannarayanre...@subexworld.com ; * >> URL:* www.subexworld.com >> >> >> >> *Disclaimer: This e-mail is bound by the terms and conditions described >> at >> **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> >> > > <><>
Re: [SQL] Oracle Equivalent queries in Postgres
2011/2/16 Thomas Kellerer : > Pavel Stehule, 16.02.2011 11:50: >> >> Try to use a standardized information_schema instead - these views are >> same on PostgreSQL and Oracle. > > Unfortunately they are not the same: Oracle does not support > INFORMATION_SCHEMA > sorry, I expected so all mature databases support it. Regards Pavel > Regards > Thomas > > > -- > 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] Function compile error
Hello please, look to page http://www.postgresql.org/docs/9.0/interactive/plpgsql-porting.html It can be faster, if you try to read PL/pgSQL documentation first. PL/pgSQL is near PL/SQL, but it is a different language and environment still. http://www.postgresql.org/docs/9.0/interactive/plpgsql.html Regards Pavel Stehule 2011/2/16 Sivannarayanreddy > Hello, > I am trying to create the function as below but it is throwing error > 'ERROR: syntax error at or near "DECLARE"', Could some one help me please > > CREATE FUNCTION check_password(databasename text, tablename text, indexname > text)RETURNS VOID AS > DECLARE v_count INTEGER; > BEGIN > select count(1) into v_count from pg_index inx where inx.indexrelid > in > (select oid from pg_class where relname=$3 and relowner in > (select oid from pg_authid where rolname=$1)) > and inx.indrelid in > (select oid from pg_class where relname=$2 and relowner in > (select oid from pg_authid where rolname=$1)); > if v_count = 0 then > execute immediate 'create unique index $3 on $2 (acn_id)'; > end if; > END; > > *Sivannarayanareddy Nusum** **| **System Analyst(Moneta GDO)* > > Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, > Bangalore – 560037, India. > *Phone:* +91 80 6696 3371; *Mobile:* +91 9902065831 *Fax:* +91 80 6696 > ; > > *Email:* sivannarayanre...@subexworld.com ; * > URL:* www.subexworld.com > > > > *Disclaimer: This e-mail is bound by the terms and conditions described at > **http://www.subexworld.com/mail-disclaimer.html*<http://www.subexworld.com/mail-disclaimer.html> > <>
Re: [SQL] Retrieve the column values of a record without knowing the names
Hello you can't simply iterate over record in plpgsql. You can use a some toolkits like PLToolkit, or different PL language like PLPerl, or some dirty trick http://okbob.blogspot.com/2010/12/iteration-over-record-in-plpgsql.html regards Pavel Stehule 2011/2/16 arthur_info : > > Hello, > > I've got the following function and I want to access the fields values of my > record by index. The problem is that my select is retrieving each record > line with all values and not each one of each row on my view... How can I > solve this problem? > > Thanks in advance. > > > CREATE FUNCTION fc_teste_tce(aluno integer) RETURNS character varying AS > $BODY$ > DECLARE > reg record; > BEGIN > for reg in execute 'SELECT ARRAY (SELECT vw_teste_tce FROM > estagio.vw_teste_tce where aluno = ''3043'' LIMIT 20) AS campos' loop > for j in 1..array_upper(reg.campos,1) loop > raise notice 'Field Value: %',reg.campos[j]; > end loop; > end loop; > return 'ok'; > END; > $BODY$ > > LANGUAGE plpgsql VOLATILE; > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Retrieve-the-column-values-of-a-record-without-knowing-the-names-tp3387932p3387932.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] Obscure behavior of ORDER BY
Hello
this behave depends on your language rules. So this behave can be ok.
pavel=# select * from (values('.'),('@'),('.xxx'),(' ')) x order by 1;
column1
─
.
@
.xxx
(4 rows)
you can se so string with space on start is on end and this is
correct, because spaces and white chars are ignored.
Regards
Pavel Stehule.
2011/3/21 Tambet Matiisen :
> Hi everyone!
>
> I recently noticed obscure behavior of ORDER BY. Consider this example:
>
> select email from
> (
> select '@'::text as email
> union all
> select '.'::text as email
> ) a
> order by email;
>
> The result is:
> email
> ---
> .
> @
> (2 rows)
>
> This is all normal - I expect, that dot is smaller than ampersand. But if I
> add anything after dot, the order is reversed:
>
> select email from
> (
> select '@'::text as email
> union all
> select '.m'::text as email
> ) a
> order by email
>
> The result is:
> email
> ---
> @
> .m
> (2 rows)
>
> Why is this happening? As dot is smaller than ampersand, anything after dot
> shouldn't matter.
>
> I'm using PostgreSQL 8.4.7 on 32-bit Debian.
>
> Thanks in advance,
> Tambet
>
> --
> 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] update with recursive query
Hello it is possible in 9.1. In older version you have to use a temp table. Regards Pavel Stehule 2011/4/14 Steven Dahlin : > Is it possible to execute an update using recursion? I need to update a set > of records and also update their children with the same value. I tried the > following query but it gave an error at the "update schema.table tbl": > > with recursive childTbl( pid, > ppid, > proc_id, > other_id ) > as ( select prc.pid, > prc.ppid, > prc.proc_id, > prc.other_id > from my_schema.prc_tbl prc > where ( ( prc.proc_path like '%stuff%' ) > or ( prc.proc_parameters like '%stuff%' ) ) > and ( prc.other_id is null ) > union all > select prcsub.pid, > prcsub.ppid, > prcsub.proc_id, > prcsub.other_id > from childTbl prcpar, > my_schema.prc_tbl prcsub > where ( prcsub.ppid = prcpar.pid ) > ) > update my_schema.prc_tbl prc > set other_id = 101 > from childTbl > > However, if I do a "select * from childTbl" it works. The docs take about > updates and talk about recursive queries with selects but nothing seems to > cover the joining of the two. > > Thanks > > -- 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] PLpgSQL variables persistance between several functions calls
Hello no, it's not possible Regards Pavel Stehule 2011/5/2 Charles N. Charotti : > Hello everybody ! > > I want to know if I could share PLpgSQL variables between different > functions and within different calls just using memory (not tables or other > methods). > > If it is really possible ? > > Thanks in advance, > > Chuck > -- 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] FOR EACH STATEMENT trigger ?
Hello, 2011/5/6 F. BROUARD / SQLpro : > > Hi there > > I am trying to get an example of SET BASED trigger logic with FOR EACH > STATEMENT, but I cannot find any example involving the pseudo table NEW > (or OLD) in the trigger function SQL statement. > PostgreSQL doesn't support NEW or OLD tables in statement triggers. You should to use ROW triggers. Regards Pavel Stehule > Let me give you a real life example. > > Suppose we have the above table : > > CREATE TABLE T_PRODUIT_DISPO_PDD > (PRD_ID INT NOT NULL, > PDD_BEGIN DATE NOT NULL, > PDD_END DATE, > PDD_QUANTITY FLOAT NOT NULL); > > We want to never have more thant one PDD_END = NULL for the same PRD_ID. > > The assertion we can do is : > > ALTER TABLE T_PRODUIT_DISPO_PDD > ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL > CHECK (NOT EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1)) > > Which is not supported by PG > > So I wuld like to do this with a FOR EACH STATEMENT trigger and not by a > FOR EACH ROW. > > Here is the code I try : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_END() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT COUNT(*) INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID IN(SELECT NEW.PRD_ID > FROM NEW) AS T > AND PDD_END IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le > couple de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > Which produce an error ! > > Of course I can do that with a FOR EACH STATEMENT like this one : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT 1 INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID = NEW.PRD_ID > AND PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le > couple de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > CREATE TRIGGER E_IU_PRD > AFTER INSERT OR UPDATE > ON T_PRODUIT_DISPO_PDD > FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN(); > > > But it is absolutly not that I Want > > Thanks > > > -- > Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 > Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com > Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence > Audit, conseil, expertise, formation, modélisation, tuning, optimisation > *** http://www.sqlspot.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] FOR EACH STATEMENT trigger ?
Hello it isn't bug. PostgreSQL doesn't support NEW and OLD tables like MSSQL does for statement triggers. Regards Pavel Stehule 2011/5/6 Frédéric BROUARD : > Hi there > > I am trying to get an example of SET BASED trigger logic with FOR EACH > STATEMENT, but I cannot find any example involving the pseudo table NEW (or > OLD) in the trigger function SQL statement. > > Let me give you a real life example. > > Suppose we have the above table : > > CREATE TABLE T_PRODUIT_DISPO_PDD > (PRD_ID INT NOT NULL, > PDD_BEGIN DATE NOT NULL, > PDD_END DATE, > PDD_QUANTITY FLOAT NOT NULL); > > We want to never have more thant one PDD_END = NULL for the same PRD_ID. > > The assertion we can do is : > > ALTER TABLE T_PRODUIT_DISPO_PDD > ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL > CHECK (NOT EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1)) > > Which is not supported by PG > > So I wuld like to do this with a FOR EACH STATEMENT trigger and not by a FOR > EACH ROW. > > Here is the code I try : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_END() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT COUNT(*) INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID IN(SELECT NEW.PRD_ID > FROM NEW) AS T > AND PDD_END IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple > de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > Which produce an error ! > > Of course I can do that with a FOR EACH STATEMENT like this one : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT 1 INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID = NEW.PRD_ID > AND PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple > de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > CREATE TRIGGER E_IU_PRD > AFTER INSERT OR UPDATE > ON T_PRODUIT_DISPO_PDD > FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN(); > > > But it is absolutly not that I Want > > Thanks > > > -- > Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 > Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com > Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence > Audit, conseil, expertise, formation, modélisation, tuning, optimisation > *** http://www.sqlspot.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] problem with selecting from a function
2011/6/21 Andreas : > Hi, > > I've got a table with a couple of objects. > Primary key object_id. > > There is a function that fetches some values from another table that relate > to an object_id. > Like fctX ( 7 ) --> set of typX ... ( 14, 'bla' ), ( 17, 'blu' ), ( 21, > 'ble' ), ... > The result of the function can have 0 or more lines of a defined result-type > typX. > Those resulting numbers are not object_ids. > > Now I'd need a SELECT that lists all function results of all object_ids. > Like: > ... > 6, ... > 7, 14, 'bla' > 7, 17, 'blu' > 7, 21, 'ble' > 8, ... > > Actually it was enough to get just the numerical column of the function > result. > > I tried > select object_id, fctX (object_id) from objects; > Then I get: > 7, (14, 'bla') > 7, (17, 'blu') > 7, (21, 'ble') <--- round brackets > This looks like an array but how can I split it up to columns or at least > extract the number-column? > this is composite value you can try SELECT object_id, (fctX(object_id)).* from objects Regards Pavel Stehule > -- > 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] a strange order by behavior
Hello a equalent of C collate for UTF8 is ucs_basic Regards Pavel Stehule 2011/6/22 Samuel Gendler : > > > On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wilde wrote: >> >> the database collation is: en_US.UTF-8 >> drop table t1; >> create table t1 (recid int ,f1 varchar(20)); >> insert into t1 values (1,'a'); >> insert into t1 values (2,' '); >> insert into t1 values (3,'aa'); >> insert into t1 values (4,' a'); >> select * from t1 order by f1 >> result: >> recid f1 >> 2 " " >> 1 "a" -- 2 comes before 1 because space is smaller then 'a'. >> fine. >> 4 " a" -- now is see that 1 comes before 4 because space is >> greater then 'a' !? >> 3 "aa" -- now again, 4 comes before 3 because space is smaller >> the 'a' !?! > > I seem to recall a thread here about it ignoring spaces entirely in that > collation (and maybe ignoring capitalization, too?). I went to go test that > assertion by initializing a database with C collation and got some complaint > about it being incompatible with my template1 template database. I > initialized a db off of template0 and then got side tracked and you've only > just reminded me of it. I was planning to test whether it is safe to use > UTF-8 for encoding but use C collation, and then maybe investigate other > collations. > This worked: > createdb -E UTF-8 --lc-collate=C some_db > so it should be easy enough to play around with it some. I'm not sure how > to get a list of valid collations for any given charset, and it seems like C > collation would generate somewhat strange results with non-ascii characters > (french accented characters are supposed to alphabetize in some unexpected > manner, I believe), so there must be something better - closer to UTF-8 > collation but without ignoring whitespace and such. A quick google search > reveals that there is some kind of standard for unicode collation > (http://www.unicode.org/reports/tr10/ ) and I have no idea if that is what > is represented by the en_US.UTF-8 collation or not. I've got no real > experience with this stuff. > It appears that there are differences regarding collation in recent versions > - the beta docs for 9.1 show that you can set collation on individual > operations or differently for different columns > (http://www.postgresql.org/docs/9.1/static/collation.html ). I see nothing > like that in 8.4 docs. > It definitely looks like we both need to have a read of the localization > chapter of the docs for our database version as there is a bunch of stuff in > there that I was surprised to read when I just did a quick scan - like using > anything but C or posix is much slower and can produce incorrect results in > a 'like' query > It looks like the docs prior to 9.1beta have no real reference to collation > at all, so it's down to trial and error unless someone in the know speaks > up. > --sam > > -- 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] a strange order by behavior
Hello Peter > Pavel suggested using a collation of ucs_basic, but I get an error when I > try that on linux: > $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test > createdb: database creation failed: ERROR: invalid locale name ucs_basic isn't this a bug in collations? Regards Pavel > I was able to create the db with --lc_collate=C and get case-sensitive > sorting that treats spaces 'correctly,' but I have no idea how reliable that > is with multibyte characters and it almost certainly doesn't handle accented -- 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] a strange order by behavior
> I'm actually surprised that european users aren't complaining about this all > the time, but maybe european users are used to seeing things ordered in a > manner which doesn't honour the 'correct' ordering of accented characters. > Actually, I wonder if the probable explanation of the lack of complaint is > the fact that the kinds of fields most apps would tend to do alphabetical > sorts on probably don't tend to have lots of punctuation other than spaces, > so perhaps the language sensitive sorts are deemed sufficient because most > people don't notice the funky behaviour with punctuation and whitespace > while case-insensitive sort is probably desired most of the time. I checked czech UTF8 collation and it is correct postgres=# select * from x order by a collate ucs_basic; a --- Chromečka Crha Semerád Syn Záruba Šebíšek (6 rows) postgres=# select * from x order by a collate "cs_CZ"; a --- Crha Chromečka Semerád Syn Šebíšek Záruba (6 rows) Regards Pavel Stehule -- 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] a strange order by behavior
2011/6/22 Peter Eisentraut : > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote: >> Pavel suggested using a collation of ucs_basic, but I get an error >> when I >> try that on linux: >> >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test >> createdb: database creation failed: ERROR: invalid locale name ucs_basic > > ucs_basic is a collation name, which is an SQL object. The argument of > createdb --lc-collate is an operating system locale name. You can't mix > the two, even though they are similar. > ok, what I can to select, when I would to use a C like default order? Regards Pavel > > -- 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] a strange order by behavior
2011/6/23 Peter Eisentraut : > On tor, 2011-06-23 at 05:57 +0200, Pavel Stehule wrote: >> 2011/6/22 Peter Eisentraut : >> > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote: >> >> Pavel suggested using a collation of ucs_basic, but I get an error >> >> when I >> >> try that on linux: >> >> >> >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test >> >> createdb: database creation failed: ERROR: invalid locale name ucs_basic >> > >> > ucs_basic is a collation name, which is an SQL object. The argument of >> > createdb --lc-collate is an operating system locale name. You can't mix >> > the two, even though they are similar. >> > >> >> ok, what I can to select, when I would to use a C like default order? > > createdb --locale=C --encoding=UTF8 > ok, thank you Pavel > > -- 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] best performance for simple dml
Hello try it and you will see. Depends on network speed, hw speed. But the most fast is using a COPY API http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html Regards Pavel Stehule 2011/6/27 chester c young > what is the best performance / best practices for frequently-used simple > dml, for example, an insert > > 1. fast-interface > > 2. prepared statement calling "insert ..." with binary parameters > > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc > takes its arguments and performs an insert using them > >
Re: [SQL] best performance for simple dml
2011/6/27 chester c young
>
> two questions:
> I thought copy was for multiple rows - is its setup cost effective for one
> row?
I expect it will be faster for one row too - it is not sql statement
if you want to understand to performance issues you have to understand to
a) network communication costs
b) SQL parsing and SQL planning costs
c) commits costs
d) other costs - triggers, referential integrity costs
>
> copy would also only be good for insert or select, not update - is this right?
sure,
If you need to call a lot of simple dml statement in cycle, then
a) try tu move it to stored function
b) if you can't to move it, then ensure, so statements will be
executed under outer transaction
slow code
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
10x faster code
exec('begin');
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
exec('commit');
Regards
Pavel Stehule
>
> --- On Mon, 6/27/11, Pavel Stehule wrote:
>
> From: Pavel Stehule
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young"
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 12:35 AM
>
> Hello
>
> try it and you will see. Depends on network speed, hw speed. But the most
> fast is using a COPY API
>
> http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
>
> Regards
>
> Pavel Stehule
>
>
> 2011/6/27 chester c young
>
> what is the best performance / best practices for frequently-used simple dml,
> for example, an insert
> 1. fast-interface
> 2. prepared statement calling "insert ..." with binary parameters
> 3. prepared statement calling "myfunc(..." with binary parameters; myfunc
> takes its arguments and performs an insert using them
>
--
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] best performance for simple dml
Hello
2011/6/27 chester c young
> forgive me for brain storming a little re copy:
>
> if there are a limited number of tables you're inserting, would there be
> anything wrong with the app opening a copy connection? ie, a connection
> initiates the copy and then stays open like a pipe for any inserts coming
> through it. visually it's a very cool paradigm, but is it actually a good
> idea?
>
depends on application. Usually you can use a connection better than just
"insert connection". I am thinking, so it doesn't carry some special - it
remove a connection cost, but nothing more. You can use a more connections
to do paralel inserts - it has a sense.
look on pgpool or other similar sw for connection pooling
Pavel
>
> --- On *Mon, 6/27/11, Pavel Stehule * wrote:
>
>
> From: Pavel Stehule
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young"
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 1:05 AM
>
> 2011/6/27 chester c young
> http://mc/compose?to=chestercyo...@yahoo.com>
> >
> >
> > two questions:
> > I thought copy was for multiple rows - is its setup cost effective for
> one row?
>
> I expect it will be faster for one row too - it is not sql statement
>
> if you want to understand to performance issues you have to understand to
>
> a) network communication costs
> b) SQL parsing and SQL planning costs
> c) commits costs
> d) other costs - triggers, referential integrity costs
>
> >
> > copy would also only be good for insert or select, not update - is this
> right?
>
> sure,
>
> If you need to call a lot of simple dml statement in cycle, then
>
> a) try tu move it to stored function
> b) if you can't to move it, then ensure, so statements will be
> executed under outer transaction
>
> slow code
>
> for(i = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
>
> 10x faster code
>
> exec('begin');
> for(i = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
> exec('commit');
>
> Regards
>
> Pavel Stehule
>
> >
> > --- On Mon, 6/27/11, Pavel Stehule
> > http://mc/compose?to=pavel.steh...@gmail.com>>
> wrote:
> >
> > From: Pavel Stehule
> > http://mc/compose?to=pavel.steh...@gmail.com>
> >
> > Subject: Re: [SQL] best performance for simple dml
> > To: "chester c young"
> > http://mc/compose?to=chestercyo...@yahoo.com>
> >
> > Cc: pgsql-sql@postgresql.org<http://mc/compose?to=pgsql-sql@postgresql.org>
> > Date: Monday, June 27, 2011, 12:35 AM
> >
> > Hello
> >
> > try it and you will see. Depends on network speed, hw speed. But the most
> fast is using a COPY API
> >
> > http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
> >
> > Regards
> >
> > Pavel Stehule
> >
> >
> > 2011/6/27 chester c young
> > http://mc/compose?to=chestercyo...@yahoo.com>
> >
> >
> > what is the best performance / best practices for frequently-used simple
> dml, for example, an insert
> > 1. fast-interface
> > 2. prepared statement calling "insert ..." with binary parameters
> > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc
> takes its arguments and performs an insert using them
> >
>
> --
> Sent via pgsql-sql mailing list
> (pgsql-sql@postgresql.org<http://mc/compose?to=pgsql-sql@postgresql.org>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
Re: [SQL] Usage of function retruning record in query
2011/7/5 gmb : > > gmb wrote: >> >> >> Thanks for the feedback, Harald. >> >> How about specifying different aliases to the resulting values? >> This will be handy when I use the same function multiple times in the same >> query. >> (the function will take another input parameters used in the calculations) >> >> E.g.: >> SELECT itemid, (calcvalues(itemid, '2011-06-06')).*, (calcvalues(itemid, >> '2011-06-07')).* FROM itemlist; >> >> itemid | calcval1 | calcval2 | calcval1 | calcval2 >> +--+--+--+-- >> 4 | 0.67 | 10.00 | 0.64 | 65.23 >> 5 | 1.55 | 45.00 | 1.23 | 23.25 >> 6 | 3.60 | 69.00 | 2.98 | 62.66 >> How will I manage unique column names for this output? >> > Hmm.. no takers? I guess not possible then? > Thanks anyway > hello try to wrap your query to subselect, npcps_201=# select 1,2,2,3; ?column? │ ?column? │ ?column? │ ?column? ──┼──┼──┼── 1 │2 │2 │3 (1 row) Time: 0.171 ms npcps_201=# select * from (select 1,2,2,3) x (a,b,c,d); a │ b │ c │ d ───┼───┼───┼─── 1 │ 2 │ 2 │ 3 (1 row) Time: 0.202 ms Regards Pavel Stehule > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Usage-of-function-retruning-record-in-query-tp4549140p4552513.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] overload
Hello
using a "window" implemented via LIMIT OFFSET is not good - it is
solution on some systems where cursors are not available, but it is
bad solution on PostgreSQL. Use a cursor instead - it is significantly
more efective with less memory requests.
Regards
Pavel Stehule
2011/7/8 Viktor Bojović :
> Thanx Wayne,
> at the end i did it that way and it works.
> The code is below.
> CREATE FUNCTION pattern_counter1("patLength" integer) RETURNS character
> varying
> LANGUAGE plperl
> AS $_X$
> my $rvCnt = spi_exec_query("select count(1) as cnt from entry");
> #my $rowCountAll = $rvCnt->{processed};
> my $row = $rvCnt->{rows}[0];
> my $rowCountAll = $row->{cnt};
> my $windowSize = 50;
> my %patterns=();
> for (my $p=0;$p<$rowCountAll;$p+=$windowSize){
> my $sql="select sequence from entry limit $windowSize offset $p";
>
> my $rv = spi_exec_query($sql);
> my $rowCount = $rv->{processed};
> my $patLen = $_[0];
> my $patt = '';
>
> foreach my $rn (0 .. $rowCount -1){
> my $row = $rv->{rows}[$rn];
> my $seq = $row->{sequence};
> for (my $x = 1;$x<=length($seq) - $patLen;$x++){
> $patt=substr($seq,$x,$patLen);
> if (! defined $patterns{$patt}) {
> $patterns{$patt}=1;
> }else{
> $patterns{$patt}++;
> }
> }
> }
> }
>
> foreach $patt (keys %patterns){
> my $sql="insert into patterns values('".$patt."',".$patterns{$patt}.")";
> spi_exec_query($sql);
> }
> return $tmp;
> $_X$;
>
>
> On Fri, Jul 8, 2011 at 8:50 PM, wrote:
>>
>> I'm have the same situation with large tables. Take a look at using a
>> cursor to fetch several thousand rows at a time. I presume what's
>> happening is that perl is attempting to create a massive list/array in
>> memory. If you use a cursor the list should only contain X number of
>> rows where X in the number specified at each fetch execution. You'll
>> need to define the cursor inside a transaction block.
>>
>> - begin transaction
>> - define the cursor
>> - fetch rows from cursor
>> - while row count from previous step > 0, execute previous step
>> - terminate transaction
>>
>> Or you could use plpgsql instead of plperl, FOR loops over result sets in
>> plpgsql implicitly use cursors... it's just a little less code.
>>
>> Hope that helps,
>> Wayne
>>
>> On Tue, Jul 05, 2011 at 10:29:03PM +0200, Viktor Bojovi?? wrote:
>> > Hi,
>> > while reading 20GB table through PL/PERL function , it constantly grows
>> > in
>> > RAM.
>> > I wanted to ask you which is the best way to read table inside that
>> > function without such memory consumption.
>> > Thanks in advance
>> >
>> > Code is here:
>> >
>> > CREATE FUNCTION pattern_counter("patLength" integer)
>> > RETURNS varchar AS
>> > $BODY$
>> > my $rv = spi_exec_query("select sequence from entry");
>> > my $rowCount = $rv->{processed};
>> > my $patLen = $_[0];
>> > my $patt = '';
>> > my %patterns=();
>> > foreach my $rn (0 .. $rowCount -1){
>> > my $row = $rv->{rows}[$rn];
>> > my $seq = $row->{sequence};
>> > for (my $x = 1;$x<=length($seq) - $patLen;$x++){
>> > $patt=substr($seq,$x,$patLen);
>> > if (! defined $patterns{$patt}) {
>> > $patterns{$patt}=1;
>> > }else{
>> > $patterns{$patt}++;
>> > }
>> > }
>> > }
>> > foreach $patt (keys %patterns){
>> > my $sql="insert into patterns
>> > values('".$patt."',".$patterns{$patt}.")";
>> > spi_exec_query($sql);
>> > }
>> > return '';
>> > $BODY$
>> > LANGUAGE plperl VOLATILE
>> > COST 100;
>> >
>> >
>> >
>> > --
>> > ---
>> > Viktor Bojovi??
>> > ---
>> > Wherever I go, Murphy goes with me
>
>
>
> --
> ---
> Viktor Bojović
> ---
> Wherever I go, Murphy goes with me
>
--
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] using explain output within pgsql
Hello 2011/7/10 Uwe Bartels : > Hi, > > I'm starting up a datawarehouse with patitioning. > my etl processes write directly into the corresponding partitions instead of > using triggers. > > The reports I run in the datawarehouse are stored in a cache within the same > database. > Now I'd like to store besides the results the dependencies to the tables > which were used to generate the report. with this information i could > invalidate cache results for the tables I'm going to import with my etl Hello try FOR l_explain IN EXPLAIN ANALYZE ... LOOP ... Regards Pavel Stehule > processes. > > explain analyze gives me the information which table or patition is read > from for each report. e.g > explain analyze (FORMAT YAML) create table cache.report234 as select > col1,col2,sum(clicks) from dwh.event_log_weekly where week >= '2011-06-27' > and week <= '2011-07-11' group by col1,col2; > > now I'd like to store the output of explain analyze in a pgsql variable for > further processing. that looks something like this. > > DO $$declare l_explain text; > begin > l_explain := explain analyze (FORMAT YAML) create table cache.report234 as > select col1,col2,sum(clicks) from dwh.event_log_weekly where week >= > '2011-06-27' and week <= '2011-07-11' group by col1,col2; > select l_explain; > end$$; > > But that doesn't work. I get a syntax error. > > Does anybody has an idea how to retrieve the output of explain within pgsql > and store this in a variable? > An alternative would be any other way to extract the information about > tables used by arbitrary sql statements. > > best regards, > Uwe > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
