Re: [SQL] Help on Procedure running external function

[Din Adrian]

1. the function get_me_next_number is runing from this procedure (same  
trans) but it's an external one ...
2. the second solution I'am using now (using temp a table to store each  
maxnrdoc value - but the temp table give me sometimes a relation with OID  
# does not exist - problem that I can only   solve by using only  
execute - but I don't think I can ...?! :))

here is the example:


create temp table MagMaxNrBon
(
magazie varchar(5),
MaxNrBon int8,
CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
);
...

select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =  
dsgroup.magazie_implicita_lansare;

if vnrbon is null
then
select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'  
,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
insert into MagMaxNrBon values  
(dsgroup.magazie_implicita_lansare,vNrBon);

else
 update MagMaxNrBon set maxnrbon=vNrBon where magazie =  
dsgroup.magazie_implicita_lansare;

end if;
...

and in this way vNrBon is correct one ... I will try to use oly execute on  
insert,update and select on temp table MagMaxNrBon  (o_gen_calc_nr_doc  
is the "get_me_next_number" function)


thank you,
Adria Din


On Mon, 04 Jul 2005 17:27:20 +0200, Zac <[EMAIL PROTECTED]> wrote:


Din Adrian wrote:
 Yes , I know what sequence is, but our procedure for generating doc   
numbers is v. big and has manny (4) parameters  and we did'nt use  
sequence  in it for this reason 

 any other advice ?
I think there is no way to have any information about non committed  
transactions.
I don't know if I understand well your problem but from what I see  
"get_me_next_number" function runs in a different transaction (Why? Is  
it an externale procedure that make its own connection to the DB?)),  
otherwise it would see the new inserted number. The better solution is  
to run "get_me_next_number" in the same transaction.
Another solution (not so good but should work) is to generate by  
yourself the number (as you do) and lock the table until you end to  
prevent others inserting documents.

I hope this helps you.
Bye


 thak you,
Adi
 On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote:


Din Adrian wrote:


Hello,
I have a 'big' problem:
I am trying to run from a procedure a function witch generate a new
document number (max from table +1 ) and after to insert a document   
with  this number, but the function returns me the same number each   
time because  the tranzaction is not finished and the inserts are  
not  commited and of  course the next document number is the same.

...
 for ...
loop
nr=get_me_next_number(params);
insert into table values (nr,...)
end loop
...
 ...
error inserting in table .. primary_key nr .
 Is any way in making the external function to 'know' that I  
inserted   another row but this insert is in a tranzaction that is  
not finish yet  ?  ar onother solution ?
 for now I 'solved'  by asking for a nr once and generate myself  
next   number (+1) but this is not a correct solution (in this time  
somebody  else  could insert a document with the same nr as the  
procedure  )

 thank you,
Adi

I think the best solution is to use a sequence, not "select max(id)  
+1  from table". Look here:

http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
Bye

---(end of  
broadcast)---

TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match





---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq





--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Create connection with Oracle database from Postgres plpgsql function

[Dinesh Pandey]

How can we create connection with Oracle database from
Postgres plpgsql function and execute some oracle stored procedure?

Thanks
Dinesh

 

 

Re: [SQL] Create connection with Oracle database from Postgres plpgsql function

[Michael Glaesemann]

On Jul 5, 2005, at 6:40 PM, Dinesh Pandey wrote:

How can we create connection with Oracle database from Postgres  
plpgsql function and execute some oracle stored procedure?


I don't think it's possible with plpgsql. Try DBI-link.


Michael Glaesemann
grzm myrealbox com



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Help on Procedure running external function

[Zac]

I think you should use 'FOR UPDATE' clause in your first "select":

select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie = 
dsgroup.magazie_implicita_lansare FOR UPDATE;


In this way you lock the rows eventually returned and no one can update 
them (or select them "for update") until your transaction finished.


Is this good for you?

However IMHO you should think your procedures to use sequences...


1. the function get_me_next_number is runing from this procedure (same  
trans) but it's an external one ...
2. the second solution I'am using now (using temp a table to store each  
maxnrdoc value - but the temp table give me sometimes a relation with 
OID  # does not exist - problem that I can only   solve by using 
only  execute - but I don't think I can ...?! :))

here is the example:


create temp table MagMaxNrBon
(
magazie varchar(5),
MaxNrBon int8,
CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
);
...

select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =  
dsgroup.magazie_implicita_lansare;

if vnrbon is null
then
select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'  
,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
insert into MagMaxNrBon values  
(dsgroup.magazie_implicita_lansare,vNrBon);

else
 update MagMaxNrBon set maxnrbon=vNrBon where magazie =  
dsgroup.magazie_implicita_lansare;

end if;
...

and in this way vNrBon is correct one ... I will try to use oly execute 
on  insert,update and select on temp table MagMaxNrBon  
(o_gen_calc_nr_doc  is the "get_me_next_number" function)


thank you,
Adria Din


On Mon, 04 Jul 2005 17:27:20 +0200, Zac <[EMAIL PROTECTED]> wrote:


Din Adrian wrote:

 Yes , I know what sequence is, but our procedure for generating 
doc   numbers is v. big and has manny (4) parameters  and we did'nt 
use  sequence  in it for this reason 

 any other advice ?


I think there is no way to have any information about non committed  
transactions.
I don't know if I understand well your problem but from what I see  
"get_me_next_number" function runs in a different transaction (Why? 
Is  it an externale procedure that make its own connection to the 
DB?)),  otherwise it would see the new inserted number. The better 
solution is  to run "get_me_next_number" in the same transaction.
Another solution (not so good but should work) is to generate by  
yourself the number (as you do) and lock the table until you end to  
prevent others inserting documents.

I hope this helps you.
Bye


 thak you,
Adi
 On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote:


Din Adrian wrote:


Hello,
I have a 'big' problem:
I am trying to run from a procedure a function witch generate a 
newdocument number (max from table +1 ) and after to insert a 
document   with  this number, but the function returns me the same 
number each   time because  the tranzaction is not finished and the 
inserts are  not  commited and of  course the next document number 
is the same.

...
 for ...
loop
nr=get_me_next_number(params);
insert into table values (nr,...)
end loop
...
 ...
error inserting in table .. primary_key nr .
 Is any way in making the external function to 'know' that I  
inserted   another row but this insert is in a tranzaction that is  
not finish yet  ?  ar onother solution ?
 for now I 'solved'  by asking for a nr once and generate myself  
next   number (+1) but this is not a correct solution (in this 
time  somebody  else  could insert a document with the same nr as 
the  procedure  )

 thank you,
Adi

I think the best solution is to use a sequence, not "select max(id)  
+1  from table". Look here:

http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
Bye

---(end of  
broadcast)---

TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match





---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq







---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Help on Procedure running external function

[Din Adrian]

I 'solved' my 'relation with OID  # does not exist' problem using only  
execute on temp table ...
Is no need for me to 'lock'(for update) the temp table - is temporary  
(lock at 'create temp table MagMaxNrBon')- everybody has his own copy 


Your right I should change the get_number procedure to create 'on the fly  
sequences' for each new user settings (in our app the user have the power  
to set the way of generating number for every type of document) (for that  
I need time wich I don't have now :) )...

anyway .. is working (not how I wanted but it's ok)

thank you,
Adrian Din


On Tue, 05 Jul 2005 13:47:24 +0200, Zac <[EMAIL PROTECTED]> wrote:


I think you should use 'FOR UPDATE' clause in your first "select":

select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =  
dsgroup.magazie_implicita_lansare FOR UPDATE;


In this way you lock the rows eventually returned and no one can update  
them (or select them "for update") until your transaction finished.


Is this good for you?

However IMHO you should think your procedures to use sequences...


1. the function get_me_next_number is runing from this procedure (same   
trans) but it's an external one ...
2. the second solution I'am using now (using temp a table to store  
each  maxnrdoc value - but the temp table give me sometimes a relation  
with OID  # does not exist - problem that I can only   solve by  
using only  execute - but I don't think I can ...?! :))

here is the example:
  create temp table MagMaxNrBon
(
magazie varchar(5),
MaxNrBon int8,
CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
);
...
 select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =   
dsgroup.magazie_implicita_lansare;

if vnrbon is null
then
select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'   
,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
insert into MagMaxNrBon values   
(dsgroup.magazie_implicita_lansare,vNrBon);

else
 update MagMaxNrBon set maxnrbon=vNrBon where magazie =   
dsgroup.magazie_implicita_lansare;

end if;
...
 and in this way vNrBon is correct one ... I will try to use oly  
execute on  insert,update and select on temp table MagMaxNrBon   
(o_gen_calc_nr_doc  is the "get_me_next_number" function)

 thank you,
Adria Din
  On Mon, 04 Jul 2005 17:27:20 +0200, Zac <[EMAIL PROTECTED]> wrote:


Din Adrian wrote:

 Yes , I know what sequence is, but our procedure for generating  
doc   numbers is v. big and has manny (4) parameters  and we did'nt  
use  sequence  in it for this reason 

 any other advice ?


I think there is no way to have any information about non committed   
transactions.
I don't know if I understand well your problem but from what I see   
"get_me_next_number" function runs in a different transaction (Why?  
Is  it an externale procedure that make its own connection to the  
DB?)),  otherwise it would see the new inserted number. The better  
solution is  to run "get_me_next_number" in the same transaction.
Another solution (not so good but should work) is to generate by   
yourself the number (as you do) and lock the table until you end to   
prevent others inserting documents.

I hope this helps you.
Bye


 thak you,
Adi
 On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote:


Din Adrian wrote:


Hello,
I have a 'big' problem:
I am trying to run from a procedure a function witch generate a  
newdocument number (max from table +1 ) and after to insert a  
document   with  this number, but the function returns me the same  
number each   time because  the tranzaction is not finished and the  
inserts are  not  commited and of  course the next document number  
is the same.

...
 for ...
loop
nr=get_me_next_number(params);
insert into table values (nr,...)
end loop
...
 ...
error inserting in table .. primary_key nr .
 Is any way in making the external function to 'know' that I   
inserted   another row but this insert is in a tranzaction that is   
not finish yet  ?  ar onother solution ?
 for now I 'solved'  by asking for a nr once and generate myself   
next   number (+1) but this is not a correct solution (in this  
time  somebody  else  could insert a document with the same nr as  
the  procedure  )

 thank you,
Adi

I think the best solution is to use a sequence, not "select max(id)   
+1  from table". Look here:

http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
Bye

---(end of   
broadcast)---

TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match





---(end of  
broadcast)---

TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq





---(end of broadcast)---

Re: [SQL] Help on Procedure running external function

[Bruno Wolff III]

On Tue, Jul 05, 2005 at 13:47:24 +0200,
  Zac <[EMAIL PROTECTED]> wrote:
> I think you should use 'FOR UPDATE' clause in your first "select":
> 
> select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie = 
> dsgroup.magazie_implicita_lansare FOR UPDATE;
> 
> In this way you lock the rows eventually returned and no one can update 
> them (or select them "for update") until your transaction finished.

That doesn't work because you don't lock rows which aren't in the table
yet. You need to use a lock table instead.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Help on Procedure running external function

[Din Adrian]

I am sorry but:
the table is TEMPORARY ... I don't need to do any lock on it 
(eventualy I should do a lock on the table where I want to insert the  
documents - the MagMaxNrBon is only a temp table used for storing the max  
document nr for each user's setings in this transaction - I did that temp  
table instead of running always the return_next_number function (the  
problem I postit first) - I run once the function for every user's  
settings - get the max doc free number, store in the temp table, do the  
insert in onother table and next time I get this number and raise by 1  
instead of running again the get_next_number function (because as I said  
-  it give the same max number (the insert into table is not 'visible') ))


...
as I said it's 'solved' ...

thank you,
Adrian Din


On Tue, 5 Jul 2005 07:39:48 -0500, Bruno Wolff III <[EMAIL PROTECTED]> wrote:


On Tue, Jul 05, 2005 at 13:47:24 +0200,
  Zac <[EMAIL PROTECTED]> wrote:

I think you should use 'FOR UPDATE' clause in your first "select":

select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare FOR UPDATE;

In this way you lock the rows eventually returned and no one can update
them (or select them "for update") until your transaction finished.


That doesn't work because you don't lock rows which aren't in the table
yet. You need to use a lock table instead.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings





--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Create connection with Oracle database from Postgres plpgsql

[Bricklen Anderson]

Dinesh Pandey wrote:
> How can we create connection with Oracle database from Postgres plpgsql
> function and execute some oracle stored procedure?
> 
> Thanks
> Dinesh
You can use perl DBI to access Oracle, providing you have DBI and the plperlu
language installed.

Sample code that may help you get started (lookout for typos):

create or replace function connect_ora() returns void as $$
use DBI;
&main;
sub main {
  my $query="select 1 from dual";
  my $dbh=openDatabase();
  if ($dbh==0) { return; }
  my $sth = $dbh->prepare( $query, {ora_check_sql => 0} ) || elog NOTICE, "Can't
prepare SQL statement: $DBI::errstr\n";
  $sth->execute() || elog ERROR, "Cant execute SQL statement: $DBI::errstr\n";
  my $array_ref = $sth->fetchall_arrayref();
  $sth->finish();
  $dbh->disconnect() || elog WARNING, "Disconnection from db failed\n";
  RETURN;
}
sub openDatabase {
$dbh =
DBI->connect_cached("dbi:Oracle:host=;sid=;port=",,)
|| elog ERROR, $DBI::errstr;
$dbh->{RowCacheSize} = 100;
  return $dbh;
}
$$ language plperlu;


Customize as you see fit. YMMV

-- 
___

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq