Thread: help with a procedure

help with a procedure

From

Carlos Carcamo

Date:

05 June 2014, 18:33:10

Hi everyone, I wonder if you could help me with a procedure that I would like to perform in postgresql.

I have an insert query like this:

INSERT INTO products (product_id, description, price, qty, ...) values ('01', 'some description', 10.15, 5, ...)

then if there is no problem, perform another query like:

INSERT INTO store(store_id, description, price, qty, ...) values ('02', 'some description', 10.15, 5, ...)

So the second query depends of the first query, if the first one succeed the second will perform the second insert

I would like to do something like:

SELECT myProcedure(product_id, p_description, price, qty, store_id, store_description );

waiting for a response from procedure, maybe true or false.

Thanks in advance...

--
"El desarrollo no es material es un estado de conciencia metal"

Re: help with a procedure

From

Carlos Carcamo

Date:

05 June 2014, 18:37:19

2014-06-05 9:32 GMT-06:00 Carlos Carcamo <eazyduiz@gmail.com>:

Hi everyone, I wonder if you could help me with a procedure that I would like to perform in postgresql.

I have an insert query like this:

INSERT INTO products (product_id, description, price, qty, ...) values ('01', 'some description', 10.15, 5, ...)

then if there is no problem, perform another query like:

INSERT INTO store(store_id, description, price, qty, ...) values ('02', 'some description', 10.15, 5, ...)

So the second query depends of the first query, if the first one succeed the second will perform the second insert

I would like to do something like:

SELECT myProcedure(product_id, p_description, price, qty, store_id, store_description );

waiting for a response from procedure, maybe true or false.

Thanks in advance...

--
"El desarrollo no es material es un estado de conciencia metal"

Sorry, the second query looks like:
INSERT INTO store(store_id, description, product_id, price, qty, ...) values ('02', 'some description', '01', 10.15, 5, ...)

--
"El desarrollo no es material es un estado de conciencia metal"

Re: help with a procedure

From

Seref Arikan

Date:

05 June 2014, 18:45:55

Hi Carlos,

Unless I'm missing something here, your queries are probably being called from a programming language (java/c# etc) and your database access api should support transactions. If you perform both operations under the same db transaction and commit your transaction things should be fine. If there is a problem with the first INSERT, your api should throw an exception and you won't be able to commit the transaction (you may/may not need to call rollback in your catch block), so it'll either be both calls executed or none.

You may want to read about how db transactions are handled in your programming environment.

Regards

Seref

On Thu, Jun 5, 2014 at 4:36 PM, Carlos Carcamo <eazyduiz@gmail.com> wrote:

2014-06-05 9:32 GMT-06:00 Carlos Carcamo <eazyduiz@gmail.com>:

Hi everyone, I wonder if you could help me with a procedure that I would like to perform in postgresql.

I have an insert query like this:

INSERT INTO products (product_id, description, price, qty, ...) values ('01', 'some description', 10.15, 5, ...)

then if there is no problem, perform another query like:

INSERT INTO store(store_id, description, price, qty, ...) values ('02', 'some description', 10.15, 5, ...)

So the second query depends of the first query, if the first one succeed the second will perform the second insert

I would like to do something like:

SELECT myProcedure(product_id, p_description, price, qty, store_id, store_description );

waiting for a response from procedure, maybe true or false.

Thanks in advance...

--
"El desarrollo no es material es un estado de conciencia metal"

Sorry, the second query looks like:
INSERT INTO store(store_id, description, product_id, price, qty, ...) values ('02', 'some description', '01', 10.15, 5, ...)

--
"El desarrollo no es material es un estado de conciencia metal"

Re: help with a procedure

From

Seref Arikan

Date:

05 June 2014, 19:13:39

Hi Carlos,

When you say procedures, do you mean calling a stored procedure you'll write from php? Or executing the individual INSERT from php sequentially?

For the first scenario, you'd need to write a postgresql stored procedure (I suggest you google: PL/pgSQL tutorial) and call the from php. For the second, well, it is exactly what I said before: you'll need to open a connection to postgres, execute your statements under a transaction and commit. I'd suggest you either search for php and postgres or ask this to a php mail group. Your goal here appears to be understanding how to call postgres from php.

Regards

Seref

On Thu, Jun 5, 2014 at 5:03 PM, Carlos Carcamo <eazyduiz@gmail.com> wrote:

Thanks for answering...
I'm using php with postgresql 9.1, I have never used procedures with php, I'm new with postgresql

2014-06-05 9:45 GMT-06:00 Seref Arikan <serefarikan@gmail.com>:

Hi Carlos,
Unless I'm missing something here, your queries are probably being called from a programming language (java/c# etc) and your database access api should support transactions. If you perform both operations under the same db transaction and commit your transaction things should be fine. If there is a problem with the first INSERT, your api should throw an exception and you won't be able to commit the transaction (you may/may not need to call rollback in your catch block), so it'll either be both calls executed or none.

You may want to read about how db transactions are handled in your programming environment.

Regards
Seref

On Thu, Jun 5, 2014 at 4:36 PM, Carlos Carcamo <eazyduiz@gmail.com> wrote:

2014-06-05 9:32 GMT-06:00 Carlos Carcamo <eazyduiz@gmail.com>:

Hi everyone, I wonder if you could help me with a procedure that I would like to perform in postgresql.

I have an insert query like this:

INSERT INTO products (product_id, description, price, qty, ...) values ('01', 'some description', 10.15, 5, ...)

then if there is no problem, perform another query like:

INSERT INTO store(store_id, description, price, qty, ...) values ('02', 'some description', 10.15, 5, ...)

So the second query depends of the first query, if the first one succeed the second will perform the second insert

I would like to do something like:

SELECT myProcedure(product_id, p_description, price, qty, store_id, store_description );

waiting for a response from procedure, maybe true or false.

Thanks in advance...

--
"El desarrollo no es material es un estado de conciencia metal"

Sorry, the second query looks like:
INSERT INTO store(store_id, description, product_id, price, qty, ...) values ('02', 'some description', '01', 10.15, 5, ...)

--
"El desarrollo no es material es un estado de conciencia metal"

--
"El desarrollo no es material es un estado de conciencia metal"

Re: help with a procedure

From

Seref Arikan

Date:

05 June 2014, 19:14:35

Sorry, I meant: "calling a stored procedure you'll write in postgres from php"

On Thu, Jun 5, 2014 at 5:13 PM, Seref Arikan <serefarikan@gmail.com> wrote:

Hi Carlos,
When you say procedures, do you mean calling a stored procedure you'll write from php? Or executing the individual INSERT from php sequentially?
For the first scenario, you'd need to write a postgresql stored procedure (I suggest you google: PL/pgSQL tutorial) and call the from php. For the second, well, it is exactly what I said before: you'll need to open a connection to postgres, execute your statements under a transaction and commit. I'd suggest you either search for php and postgres or ask this to a php mail group. Your goal here appears to be understanding how to call postgres from php.

Regards
Seref

On Thu, Jun 5, 2014 at 5:03 PM, Carlos Carcamo <eazyduiz@gmail.com> wrote:
Thanks for answering...
I'm using php with postgresql 9.1, I have never used procedures with php, I'm new with postgresql

2014-06-05 9:45 GMT-06:00 Seref Arikan <serefarikan@gmail.com>:

Hi Carlos,
Unless I'm missing something here, your queries are probably being called from a programming language (java/c# etc) and your database access api should support transactions. If you perform both operations under the same db transaction and commit your transaction things should be fine. If there is a problem with the first INSERT, your api should throw an exception and you won't be able to commit the transaction (you may/may not need to call rollback in your catch block), so it'll either be both calls executed or none.

You may want to read about how db transactions are handled in your programming environment.

Regards
Seref

On Thu, Jun 5, 2014 at 4:36 PM, Carlos Carcamo <eazyduiz@gmail.com> wrote:

2014-06-05 9:32 GMT-06:00 Carlos Carcamo <eazyduiz@gmail.com>:

Hi everyone, I wonder if you could help me with a procedure that I would like to perform in postgresql.

I have an insert query like this:

INSERT INTO products (product_id, description, price, qty, ...) values ('01', 'some description', 10.15, 5, ...)

then if there is no problem, perform another query like:

INSERT INTO store(store_id, description, price, qty, ...) values ('02', 'some description', 10.15, 5, ...)

So the second query depends of the first query, if the first one succeed the second will perform the second insert

I would like to do something like:

SELECT myProcedure(product_id, p_description, price, qty, store_id, store_description );

waiting for a response from procedure, maybe true or false.

Thanks in advance...

--
"El desarrollo no es material es un estado de conciencia metal"

Sorry, the second query looks like:
INSERT INTO store(store_id, description, product_id, price, qty, ...) values ('02', 'some description', '01', 10.15, 5, ...)

--
"El desarrollo no es material es un estado de conciencia metal"

--
"El desarrollo no es material es un estado de conciencia metal"

Re: help with a procedure

From

Carlos Carcamo

Date:

05 June 2014, 19:26:16

What I need is some help with the procedure in postgres, I have searched in google, but I dont know how to do it, the goal is perform the first insert and then the second insert if the first one succeed in one procedure instead of using two separate inserts.

Do you have some code like that?

2014-06-05 10:14 GMT-06:00 Seref Arikan <serefarikan@gmail.com>:

Sorry, I meant: "calling a stored procedure you'll write in postgres from php"

On Thu, Jun 5, 2014 at 5:13 PM, Seref Arikan <serefarikan@gmail.com> wrote:
Hi Carlos,
When you say procedures, do you mean calling a stored procedure you'll write from php? Or executing the individual INSERT from php sequentially?
For the first scenario, you'd need to write a postgresql stored procedure (I suggest you google: PL/pgSQL tutorial) and call the from php. For the second, well, it is exactly what I said before: you'll need to open a connection to postgres, execute your statements under a transaction and commit. I'd suggest you either search for php and postgres or ask this to a php mail group. Your goal here appears to be understanding how to call postgres from php.

Regards
Seref

On Thu, Jun 5, 2014 at 5:03 PM, Carlos Carcamo <eazyduiz@gmail.com> wrote:
Thanks for answering...
I'm using php with postgresql 9.1, I have never used procedures with php, I'm new with postgresql

2014-06-05 9:45 GMT-06:00 Seref Arikan <serefarikan@gmail.com>:

Hi Carlos,
Unless I'm missing something here, your queries are probably being called from a programming language (java/c# etc) and your database access api should support transactions. If you perform both operations under the same db transaction and commit your transaction things should be fine. If there is a problem with the first INSERT, your api should throw an exception and you won't be able to commit the transaction (you may/may not need to call rollback in your catch block), so it'll either be both calls executed or none.

You may want to read about how db transactions are handled in your programming environment.

Regards
Seref

On Thu, Jun 5, 2014 at 4:36 PM, Carlos Carcamo <eazyduiz@gmail.com> wrote:

2014-06-05 9:32 GMT-06:00 Carlos Carcamo <eazyduiz@gmail.com>:

Hi everyone, I wonder if you could help me with a procedure that I would like to perform in postgresql.

I have an insert query like this:

INSERT INTO products (product_id, description, price, qty, ...) values ('01', 'some description', 10.15, 5, ...)

then if there is no problem, perform another query like:

INSERT INTO store(store_id, description, price, qty, ...) values ('02', 'some description', 10.15, 5, ...)

So the second query depends of the first query, if the first one succeed the second will perform the second insert

I would like to do something like:

SELECT myProcedure(product_id, p_description, price, qty, store_id, store_description );

waiting for a response from procedure, maybe true or false.

Thanks in advance...

--
"El desarrollo no es material es un estado de conciencia metal"

Sorry, the second query looks like:
INSERT INTO store(store_id, description, product_id, price, qty, ...) values ('02', 'some description', '01', 10.15, 5, ...)

--
"El desarrollo no es material es un estado de conciencia metal"

--
"El desarrollo no es material es un estado de conciencia metal"

--
"El desarrollo no es material es un estado de conciencia metal"

Re: help with a procedure

From

David G Johnston

Date:

05 June 2014, 19:39:50

Have you read this chapter of the documentation?

http://www.postgresql.org/docs/9.3/interactive/plpgsql.html


Carlos Carcamo wrote
> What I need is some help with the procedure in postgres, I have searched
> in
> google, but I dont know how to do it, the goal is perform the first insert
> and then the second insert if the first one succeed in one procedure
> instead of using two separate inserts.
>
> Do you have some code like that?
>
> 2014-06-05 10:14 GMT-06:00 Seref Arikan <

> serefarikan@

> >:
>
>> Sorry, I meant: "calling a stored procedure you'll write in postgres from
>> php"
>>
>>
>> On Thu, Jun 5, 2014 at 5:13 PM, Seref Arikan <

> serefarikan@

> >
>> wrote:
>>
>>> Hi Carlos,
>>> When you say procedures, do you mean calling a stored procedure you'll
>>> write from php? Or executing the individual INSERT from php
>>> sequentially?
>>> For the first scenario, you'd need to write a postgresql stored
>>> procedure
>>> (I suggest you google: PL/pgSQL tutorial) and call the from php. For the
>>> second, well, it is exactly what I said before: you'll need to open a
>>> connection to postgres, execute your statements under a transaction and
>>> commit. I'd suggest you either search for php and postgres or ask this
>>> to a
>>> php mail group. Your goal here appears to be understanding how to call
>>> postgres from php.
>>>
>>> Regards
>>> Seref
>>>
>>>
>>>
>>>
>>> On Thu, Jun 5, 2014 at 5:03 PM, Carlos Carcamo <

> eazyduiz@

> >
>>> wrote:
>>>
>>>> Thanks for answering...
>>>> I'm using php with postgresql 9.1, I have never used procedures with
>>>> php, I'm new with postgresql
>>>>
>>>>
>>>> 2014-06-05 9:45 GMT-06:00 Seref Arikan <

> serefarikan@

> >:
>>>>
>>>> Hi Carlos,
>>>>> Unless I'm missing something here, your queries are probably being
>>>>> called from a programming language (java/c# etc) and your database
>>>>> access
>>>>> api should support transactions. If you perform both operations under
>>>>> the
>>>>> same db transaction and commit your transaction things should be fine.
>>>>> If
>>>>> there is a problem with the first INSERT, your api should throw an
>>>>> exception and you won't be able to commit the transaction (you may/may
>>>>> not
>>>>> need to call rollback in your catch block), so it'll either be both
>>>>> calls
>>>>> executed or none.
>>>>>
>>>>> You may want to read about how db transactions are handled in your
>>>>> programming environment.
>>>>>
>>>>> Regards
>>>>> Seref
>>>>>
>>>>>
>>>>>
>>>>> On Thu, Jun 5, 2014 at 4:36 PM, Carlos Carcamo <

> eazyduiz@

> >
>>>>> wrote:
>>>>>
>>>>>>
>>>>>> 2014-06-05 9:32 GMT-06:00 Carlos Carcamo <

> eazyduiz@

> >:
>>>>>>
>>>>>> Hi everyone, I wonder if you could help me with a procedure that I
>>>>>>> would like to perform in postgresql.
>>>>>>>
>>>>>>> I have an insert query like this:
>>>>>>>
>>>>>>> INSERT INTO products (product_id, description, price, qty, ...)
>>>>>>> values ('01', 'some description', 10.15, 5, ...)
>>>>>>>
>>>>>>> then if there is no problem, perform another query like:
>>>>>>>
>>>>>>> INSERT INTO store(store_id, description, price, qty, ...) values
>>>>>>> ('02', 'some description', 10.15, 5, ...)
>>>>>>>
>>>>>>> So the second query depends of the first query, if the first one
>>>>>>> succeed the second will perform the second insert
>>>>>>>
>>>>>>> I would like to do something like:
>>>>>>>
>>>>>>> SELECT myProcedure(product_id, p_description, price, qty, store_id,
>>>>>>> store_description );
>>>>>>>
>>>>>>> waiting for a response from procedure, maybe true or false.
>>>>>>>
>>>>>>> Thanks in advance...
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> "El desarrollo no es material es un estado de conciencia metal"
>>>>>>>
>>>>>>
>>>>>> Sorry,  the second query looks like:
>>>>>> INSERT INTO store(store_id, description, product_id, price, qty, ...)
>>>>>> values ('02', 'some description', '01', 10.15, 5, ...)
>>>>>>
>>>>>> --
>>>>>> "El desarrollo no es material es un estado de conciencia metal"
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> "El desarrollo no es material es un estado de conciencia metal"
>>>>
>>>
>>>
>>
>
>
> --
> "El desarrollo no es material es un estado de conciencia metal"





--
View this message in context: http://postgresql.1045698.n5.nabble.com/help-with-a-procedure-tp5806213p5806230.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: help with a procedure

From

Carlos Carcamo

Date:

05 June 2014, 19:46:45

Just a little, I will read it again, thanks for your help.

2014-06-05 10:39 GMT-06:00 David G Johnston <david.g.johnston@gmail.com>:

Have you read this chapter of the documentation?

http://www.postgresql.org/docs/9.3/interactive/plpgsql.html

Carlos Carcamo wrote
> What I need is some help with the procedure in postgres, I have searched
> in
> google, but I dont know how to do it, the goal is perform the first insert
> and then the second insert if the first one succeed in one procedure
> instead of using two separate inserts.
>
> Do you have some code like that?
>
> 2014-06-05 10:14 GMT-06:00 Seref Arikan <

> serefarikan@

> >:
>
>> Sorry, I meant: "calling a stored procedure you'll write in postgres from
>> php"
>>
>>
>> On Thu, Jun 5, 2014 at 5:13 PM, Seref Arikan <

> serefarikan@

> >
>> wrote:
>>
>>> Hi Carlos,
>>> When you say procedures, do you mean calling a stored procedure you'll
>>> write from php? Or executing the individual INSERT from php
>>> sequentially?
>>> For the first scenario, you'd need to write a postgresql stored
>>> procedure
>>> (I suggest you google: PL/pgSQL tutorial) and call the from php. For the
>>> second, well, it is exactly what I said before: you'll need to open a
>>> connection to postgres, execute your statements under a transaction and
>>> commit. I'd suggest you either search for php and postgres or ask this
>>> to a
>>> php mail group. Your goal here appears to be understanding how to call
>>> postgres from php.
>>>
>>> Regards
>>> Seref
>>>
>>>
>>>
>>>
>>> On Thu, Jun 5, 2014 at 5:03 PM, Carlos Carcamo <

> eazyduiz@

> >
>>> wrote:
>>>
>>>> Thanks for answering...
>>>> I'm using php with postgresql 9.1, I have never used procedures with
>>>> php, I'm new with postgresql
>>>>
>>>>
>>>> 2014-06-05 9:45 GMT-06:00 Seref Arikan <

> serefarikan@

> >:
>>>>
>>>> Hi Carlos,
>>>>> Unless I'm missing something here, your queries are probably being
>>>>> called from a programming language (java/c# etc) and your database
>>>>> access
>>>>> api should support transactions. If you perform both operations under
>>>>> the
>>>>> same db transaction and commit your transaction things should be fine.
>>>>> If
>>>>> there is a problem with the first INSERT, your api should throw an
>>>>> exception and you won't be able to commit the transaction (you may/may
>>>>> not
>>>>> need to call rollback in your catch block), so it'll either be both
>>>>> calls
>>>>> executed or none.
>>>>>
>>>>> You may want to read about how db transactions are handled in your
>>>>> programming environment.
>>>>>
>>>>> Regards
>>>>> Seref
>>>>>
>>>>>
>>>>>
>>>>> On Thu, Jun 5, 2014 at 4:36 PM, Carlos Carcamo <

> eazyduiz@

> >
>>>>> wrote:
>>>>>
>>>>>>
>>>>>> 2014-06-05 9:32 GMT-06:00 Carlos Carcamo <

> eazyduiz@

> >:
>>>>>>
>>>>>> Hi everyone, I wonder if you could help me with a procedure that I
>>>>>>> would like to perform in postgresql.
>>>>>>>
>>>>>>> I have an insert query like this:
>>>>>>>
>>>>>>> INSERT INTO products (product_id, description, price, qty, ...)
>>>>>>> values ('01', 'some description', 10.15, 5, ...)
>>>>>>>
>>>>>>> then if there is no problem, perform another query like:
>>>>>>>
>>>>>>> INSERT INTO store(store_id, description, price, qty, ...) values
>>>>>>> ('02', 'some description', 10.15, 5, ...)
>>>>>>>
>>>>>>> So the second query depends of the first query, if the first one
>>>>>>> succeed the second will perform the second insert
>>>>>>>
>>>>>>> I would like to do something like:
>>>>>>>
>>>>>>> SELECT myProcedure(product_id, p_description, price, qty, store_id,
>>>>>>> store_description );
>>>>>>>
>>>>>>> waiting for a response from procedure, maybe true or false.
>>>>>>>
>>>>>>> Thanks in advance...
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> "El desarrollo no es material es un estado de conciencia metal"
>>>>>>>
>>>>>>
>>>>>> Sorry, the second query looks like:
>>>>>> INSERT INTO store(store_id, description, product_id, price, qty, ...)
>>>>>> values ('02', 'some description', '01', 10.15, 5, ...)
>>>>>>
>>>>>> --
>>>>>> "El desarrollo no es material es un estado de conciencia metal"
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> "El desarrollo no es material es un estado de conciencia metal"
>>>>
>>>
>>>
>>
>
>
> --
> "El desarrollo no es material es un estado de conciencia metal"

--
View this message in context: http://postgresql.1045698.n5.nabble.com/help-with-a-procedure-tp5806213p5806230.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
"El desarrollo no es material es un estado de conciencia metal"

Re: help with a procedure

From

David G Johnston

Date:

05 June 2014, 21:28:19

Carlos Carcamo wrote
> SELECT myProcedure(product_id, p_description, price, qty, store_id,
> store_description );
>
> waiting for a response from procedure, maybe true or false.

Note that forcing the procedure to return false instead of simply throwing
an error is going to degrade performance.  If you can live with
silence=success; error=failure it will be much easier to program and will
achieve maximum performance.

David J.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/help-with-a-procedure-tp5806213p5806242.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: help with a procedure

From

Adrian Klaver

Date:

06 June 2014, 03:29:17

On 06/05/2014 09:45 AM, Carlos Carcamo wrote:
> Just a little, I will read it again, thanks for your help.

In particular:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

>
>



--
Adrian Klaver
adrian.klaver@aklaver.com