Re: [SQL] function that returns a set of records and integer(both of them)
2008/7/11 daniel blanco [EMAIL PROTECTED]: Hi Everyone I would like to know if i can create a function that returns a set of record with the sql statement: select and a integer, i mean both of them, because i remenber that in sql server (transact sql) i can do that in a stored procedure doing a select statement and a return of a integer in the same procedure, when I worked with T-SQL this wasn't possible - stored procedure should return recordset or multirecord set or OUT variables. And it was solution. When I would returns some similar like you, I had to return two recordsets. i'm migrating to postgresql and i have stored procedures like this in my sql server database, can i do that with postgresql? You cannot return integer and recordset from function now. There is one workaround, you can return set of cursors. Regards Pavel Stehule Thanks. Get news, entertainment and everything you care about at Live.com. Check it out! -- 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] Rollback in Postgres
On Fri, 2008-07-11 at 18:56 -0700, Lewis Cunningham wrote: In addition to allowing you to read old data, Flashback will allow you to rollback to a point in time, including returning a single table to a specific state. Flashback database is like PITR without the log files. Like I said: you cannot rollback a single transaction after commit. Please don't put links to copyrighted material on our lists. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Unnecessary repeat condition for a self inner join
While the optimizer theoretically could deduce the extra restriction
condition, it doesn't attempt to. It's extremely unclear that the extra
cycles to look for such cases would be repaid on average, because cases
like this aren't that common. The current state of affairs is that
the system will deduce implied equality conditions, but not implied
inequality conditions.
One good thing is that the equality conditions are taken care of. But I fail
to understand why do you believe that the second case is rare. I think the
optimizer would (in all self-join inequality conditions) tend towards a
table scan, which for a large table is a disaster. (Of course, the index
scan would help only if the result-set is small)
Besides, I did a simple test and although you are right about the optimizer
deducing implied equality conditions, this holds true only for a direct
join. In the second query, the optimizer recommends a table scan even for a
simple IN() condition.
Is that normal ?
Regards,
*Robins Tharakan*
Query 1:
SELECT n1.scheme_code
FROM nav n1
INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code
WHERE n1.scheme_code = 290
Nested Loop (cost=0.00..16147232.47 rows=4796100 width=4)
- Index Scan using nav__schemecode_date_lookup3b on nav n1
(cost=0.00..7347.91 rows=2190 width=4)
Index Cond: (scheme_code = 290)
- Index Scan using nav__schemecode_date_lookup3b on nav n2
(cost=0.00..7347.91 rows=2190 width=4)
Index Cond: (290 = scheme_code)
Query 2:
SELECT n1.scheme_code
FROM nav n1
INNER JOIN nav n2 ON n1.scheme_code = n2.scheme_code
WHERE n1.scheme_code IN (1, 2)
Hash Join (cost=206004.00..431864.83 rows=10720451 width=4)
Hash Cond: (n1.scheme_code = n2.scheme_code)
- Bitmap Heap Scan on nav n1 (cost=139.62..13663.13 rows=4378
width=4)
Recheck Cond: (scheme_code = ANY ('{1,2}'::integer[]))
- Bitmap Index Scan on nav__schemecode_date_lookup3b
(cost=0.00..138.53 rows=4378 width=0)
Index Cond: (scheme_code = ANY ('{1,2}'::integer[]))
- Hash (cost=112078.06..112078.06 rows=5395306 width=4)
- Seq Scan on nav n2 (cost=0.00..112078.06 rows=5395306
width=4)
Re: [SQL] Rollback in Postgres
On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs [EMAIL PROTECTED] wrote: Please don't put links to copyrighted material on our lists. That's an odd thing to say, given that virtually every link on our lists probably points to material copyrighted in some way. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] Problem in dynamic query execution in plpgsql
Hai Kretschmer , Thanks for your reply, it help to solve my problem. I have few for doubts regarding dynamic query I have a table structure: Column | Type | Modifiers +--+--- mf | double precision | sf | double precision | comm | integer | I create a the following funtion create or replace function test_perc() returns setof record as $body$ declare vchr_query VARCHAR(100); r record; begin --vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) as flt_claim from calc'; vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim'; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; RETURN; end$body$ language 'plpgsql' function created but when I am traing to run this function I got the following error ERROR: syntax error at or near – at character 18 QUERY: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim CONTEXT: PL/pgSQL function test_perc line 7 at for over execute statement LINE 1: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as... How i can solve this ? I tried another method to solve this I create a function to calculate the value create or replace function calc_perse(double precision,double precision,double precision) returns double precision as $body$ declare res double precision ; begin res :=( $1 - $1* $3/100 )- ($2 - $2*$3/100); return res; end$body$ language 'plpgsql' Then i try to call it from another function vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) as flt_claim from calc'; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; It also faild, How I can call a function like this from other function? thanks in advance Anoop G
Re: [SQL] Rollback in Postgres
On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote: On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs [EMAIL PROTECTED] wrote: Please don't put links to copyrighted material on our lists. That's an odd thing to say, given that virtually every link on our lists probably points to material copyrighted in some way. Prudence is all I ask for. We don't need to provide additional advertising for others, nor do we wish to embroil ourselves in accusations over copyright violations. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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 in dynamic query execution in plpgsql
On lau, 2008-07-12 at 14:45 +0530, Anoop G wrote: Hai Kretschmer , Thanks for your reply, it help to solve my problem. I have few for doubts regarding dynamic query ... vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim'; the '–' characters here probably are not what you think. try with '-' ... but when I am traing to run this function I got the following error ERROR: syntax error at or near – at character 18 QUERY: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim gnari -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] CHAR to INT
I'm trying to make a PL function to pass from an alphanumeric string to a numeric one changing the chars in the string to it's ORD (the inverse of the chr function) and I'm stumbling with two problems: 1) How to know if the CHAR is numeric or not. 2) If it's not numeric I want to get it's ord, but I can't find something like that in PG. Any ideas? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] Rollback in Postgres
On Sat, Jul 12, 2008 at 3:20 AM, Simon Riggs [EMAIL PROTECTED] wrote: On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote: On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs [EMAIL PROTECTED] wrote: Please don't put links to copyrighted material on our lists. That's an odd thing to say, given that virtually every link on our lists probably points to material copyrighted in some way. Prudence is all I ask for. We don't need to provide additional advertising for others, nor do we wish to embroil ourselves in accusations over copyright violations. Neither do we need to bury our heads in the sand and not know what's happening in the world at large around us. I appreciated the links to the Oracle docs. I can't see how a link to oracle documentation that is open and requires no login can be an issue of copyright violation. I might see some issue of poisoning the well as regards patented methods getting into pgsql, but that's about it. As for advertising, I doubt there's anyone on this list that just decided to switch to Oracle over just those links. What I would appreciate as regards Oracle's flashback technology would have been a link to a well written review showing the warts as well as the beauty. I've found that Oracle stuff sounds good on paper, and turns into a giant maintenance nightmare upon deployment. But that's just what I've seen looking over Oracle DBA shoulders in the past. -- 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] CHAR to INT
Hello
2008/7/12 Martín Marqués [EMAIL PROTECTED]:
I'm trying to make a PL function to pass from an alphanumeric string
to a numeric one changing the chars in the string to it's ORD (the
inverse of the chr function) and I'm stumbling with two problems:
1) How to know if the CHAR is numeric or not.
2) If it's not numeric I want to get it's ord, but I can't find
something like that in PG.
postgres=# select '1' between '0' and '9';
?column?
--
t
(1 row)
postgres=# select 'a' between '0' and '9';
?column?
--
f
(1 row)
postgres=# select ascii('7') - ascii('0');
?column?
--
7
(1 row)
Regards
Pavel Stehule
Any ideas?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
--
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] CHAR to INT
2008/7/12 Pavel Stehule [EMAIL PROTECTED]:
Hello
2008/7/12 Martín Marqués [EMAIL PROTECTED]:
I'm trying to make a PL function to pass from an alphanumeric string
to a numeric one changing the chars in the string to it's ORD (the
inverse of the chr function) and I'm stumbling with two problems:
1) How to know if the CHAR is numeric or not.
2) If it's not numeric I want to get it's ord, but I can't find
something like that in PG.
postgres=# select '1' between '0' and '9';
?column?
--
t
(1 row)
postgres=# select 'a' between '0' and '9';
?column?
--
f
(1 row)
postgres=# select ascii('7') - ascii('0');
?column?
--
7
(1 row)
Saw the ascii function when I had already sent the mail. :-(
Thanks any way.
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
--
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] Unnecessary repeat condition for a self inner join
Robins Tharakan [EMAIL PROTECTED] writes: Besides, I did a simple test and although you are right about the optimizer deducing implied equality conditions, this holds true only for a direct join. In the second query, the optimizer recommends a table scan even for a simple IN() condition. An IN is not an equivalence condition. 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
Re: [SQL] Rollback in Postgres
Please don't put links to copyrighted material on our lists. Postgres docs are copyrighted. The oracle docs are free to access just like the postgres docs. What is the issue? LewisC -- 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] Rollback in Postgres
--- On Sat, 7/12/08, Scott Marlowe [EMAIL PROTECTED] wrote: What I would appreciate as regards Oracle's flashback technology would have been a link to a well written review showing the warts as well as the beauty. I've found that Oracle stuff sounds good on paper, and turns into a giant maintenance nightmare upon deployment. But that's just what I've seen looking over Oracle DBA shoulders in the past. Oracle-base is a site I trust and use. Tim writes very good articles and this is one he did recently covering flashback in 11g. The example on flashback transaction is the best I've seen. http://www.oracle-base.com/articles/11g/FlashbackAndLogminerEnhancements_11gR1.php Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ Postgres Forums http://postgres.enterprisedb.com/forum.do -- 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] Copyright and Paper walls (was: Rollback in Postgres)
At 11:59 AM 7/12/2008, [EMAIL PROTECTED] wrote: Date: Sat, 12 Jul 2008 10:20:37 +0100 From: Simon Riggs [EMAIL PROTECTED] To: Dave Page [EMAIL PROTECTED] Cc: Lewis Cunningham [EMAIL PROTECTED], Scott Marlowe [EMAIL PROTECTED], samantha mahindrakar [EMAIL PROTECTED], pgsql-sql@postgresql.org Subject: Re: Rollback in Postgres Message-ID: [EMAIL PROTECTED] On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote: On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs [EMAIL PROTECTED] wrote: Please don't put links to copyrighted material on our lists. That's an odd thing to say, given that virtually every link on our lists probably points to material copyrighted in some way. Prudence is all I ask for. We don't need to provide additional advertising for others, nor do we wish to embroil ourselves in accusations over copyright violations. I don't want to pile more wood on the fire, but I think I can see both sides to this. I believe this is not so much copyright violation concern, but if the Pg team releases some cool feature relating to rollbacks down-the-road that is vaguely similar to Oracle's system, reducing the amount of discussion about Oracle's features on this list would reduce Oracle's ability to claim that the feature was a direct appropriation. That said (and IANAL), I think posting links to for-profit and/or copyrighted websites is really important in general for the list. There's a lot of good information out there and I think it's not so great if this list were to limit itself only to public domain and open copyright documentation for consideration. Just two more cents from the peanut gallery on a Saturday afternoon, Steve -- 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] Copyright and Paper walls
Steve writes: I don't want to pile more wood on the fire, but I think I can see both sides to this. I believe this is not so much copyright violation concern, but if the Pg team releases some cool feature relating to rollbacks down-the-road that is vaguely similar to Oracle's system, reducing the amount of discussion about Oracle's features on this list would reduce Oracle's ability to claim that the feature was a direct appropriation. So what if it is direct appropriation? Either it is patented, in which case you infringe whether you looked at their docs or not, or it isn't, in which case they have no grounds for action. There is nothing wrong with discussing Oracle's features or even deliberately duplicating them. -- John Hasler [EMAIL PROTECTED] Elmwood, WI USA -- 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 that returns a set of records and int eger(both of them)
Ok, thanks pavel. i think i'll try set of cursors. by the way in t-sql i did it as follows: table users with a field called name create procedure list_user_by_name @info_name varchar as declare @sw begin select @n=(select count(*) from users where name like (@info_name+'%')) if @sw0 begin select * where name like (@info_name+'%') return 1 end else begin return 0 end end as you see i do a select and a return at the same time when de sw variable is than 0. i was expecting to do a similiar function with plpgsql o sql in postgresql Date: Sat, 12 Jul 2008 09:05:45 +0200 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: [SQL] function that returns a set of records and integer(both of them) CC: pgsql-sql@postgresql.org 2008/7/11 daniel blanco [EMAIL PROTECTED]: Hi Everyone I would like to know if i can create a function that returns a set of record with the sql statement: select and a integer, i mean both of them, because i remenber that in sql server (transact sql) i can do that in a stored procedure doing a select statement and a return of a integer in the same procedure, when I worked with T-SQL this wasn't possible - stored procedure should return recordset or multirecord set or OUT variables. And it was solution. When I would returns some similar like you, I had to return two recordsets. i'm migrating to postgresql and i have stored procedures like this in my sql server database, can i do that with postgresql? You canno t return integer and recordset from function now. There is one workaround, you can return set of cursors. Regards Pavel Stehule Thanks. Get news, entertainment and everything you care about at Live.com. Check it out! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql _ Connect to the next generation of MSN Messenger http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-ussource=wlmailtagline
Re: [SQL] function that returns a set of records and integer(both of them)
Hello 2008/7/13 daniel blanco [EMAIL PROTECTED]: Ok, thanks pavel. i think i'll try set of cursors. by the way in t-sql i did it as follows: table users with a field called name create procedure list_user_by_name @info_name varchar as declare @sw begin select @n=(select count(*) from users where name like (@info_name+'%')) if @sw0 begin select * where name like (@info_name+'%') return 1 end else begin return 0 end end do you thing return_status or global variables? It doesn't exist in PostreSQL. Your sample is typical example, what can be in stored procedure for MS and what would not be on Oracle like rdbms. This code is little bit ineffective. You have to call seq scan on users two times. Minimally this code on pg and oracle is: create function list_user_by_name(info_name) returns users as $$ select * from users where name like $1 || '%' $$ language sql strict; -- you don't need logical info about returned set - this information is inside returned set, it is just empty or not empty. but it's better use directly well select than stored procedure in this case (8.4 will be different) - On Oracle like rdbsm SELECTs are not wrapped to procedures. p.s. postgresql doesn't support global (session variables) - this topic was discussed in different thread this week Regards Pavel as you see i do a select and a return at the same time when de sw variable is than 0. i was expecting to do a similiar function with plpgsql o sql in postgresql Date: Sat, 12 Jul 2008 09:05:45 +0200 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: [SQL] function that returns a set of records and integer(both of them) CC: pgsql-sql@postgresql.org 2008/7/11 daniel blanco [EMAIL PROTECTED]: Hi Everyone I would like to know if i can create a function that returns a set of record with the sql statement: select and a integer, i mean both of them, because i remenber that in sql server (transact sql) i can do that in a stored procedure doing a select statement and a return of a integer in the same procedure, when I worked with T-SQL this wasn't possible - stored procedure should return recordset or multirecord set or OUT variables. And it was solution. When I would returns some similar like you, I had to re turn two recordsets. i'm migrating to postgresql and i have stored procedures like this in my sql server database, can i do that with postgresql? You cannot return integer and recordset from function now. There is one workaround, you can return set of cursors. Regards Pavel Stehule Thanks. Get news, entertainment and everything you care about at Live.com. Check it out! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql Connect to the next generation of MSN Messenger Get it now! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
