Thread: Deletion

On 08/27/2014 08:24 AM, Ramesh T wrote:
> any help...

Honestly, this is basic programming/scripting. I would suggest picking
up an introductory programming book to get the basics down.
In the meantime, you have used a parameter variable ($1) with out
actually supplying a parameter. So Postgres has no idea what $1
represents. Either hard code the part.custid value i.e part.custid = 1
or find a way to supply the value. How you do that is going to depend on
how you are going to use the script and where you are expecting to pull
the value from.

> thanks,
> ram
>
>
> On Wed, Aug 27, 2014 at 3:22 PM, Ramesh T <rameshparnanditech@gmail.com
> <mailto:rameshparnanditech@gmail.com>> wrote:
>
>     Hi,
>                  I have deletion script to delete particular cust from
>     database.I was saved a file name with custde.sql.i need to run from
>     command line like putty tool..But have a problem
>
>     example :
>
>     delete from part where part.custid=$1;
>
>     when i ran custde.sql from putty tool
>
>     db=#\i custde.sql
>
>     it's return error
>       psql:custde.sql:9: ERROR:  there is no parameter $1
>     LINE 1: ..._ID from part where  part.CUST_ID = $1);
>
>     please let me know how to run script ..and how to give parameter..
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Ramesh T wrote
> Hi,
>             I have deletion script to delete particular cust from
> database.I was
> saved a file name with custde.sql.i need to run from command line like
> putty tool..But have a problem
>
> example :
>
> delete from part where part.custid=$1;
>
> when i ran custde.sql from putty tool
>
> db=#\i custde.sql
>
> it's return error
>  psql:custde.sql:9: ERROR:  there is no parameter $1
> LINE 1: ..._ID from part where  part.CUST_ID = $1);
>
> please let me know how to run script ..and how to give parameter..

psql doesn't use parameters - it uses variables.

http://www.postgresql.org/docs/9.3/interactive/app-psql.html

In part <and not tested>:

echo "DELETE FROM part WHERE part.CUST_ID = :'del_id';" > custde.sql
psql --set=del_id=383373 --file=custde.sql

Note that based on your prompt (db=#) and the command "\i" you are already
using psql interactively.     This not the same thing as "I need to run from
command line like putty tool".

Beyond this you need to learn about shell scripting to more readily
integrate psql into a complex process.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Deletion-tp5816588p5816617.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 08/27/2014 08:24 AM, Ramesh T wrote:

any help...

Honestly, this is basic programming/scripting. I would suggest picking up an introductory programming book to get the basics down.
In the meantime, you have used a parameter variable ($1) with out actually supplying a parameter. So Postgres has no idea what $1 represents. Either hard code the part.custid value i.e part.custid = 1 or find a way to supply the value. How you do that is going to depend on how you are going to use the script and where you are expecting to pull the value from.

thanks,
ram


On Wed, Aug 27, 2014 at 3:22 PM, Ramesh T <rameshparnanditech@gmail.com
<mailto:rameshparnanditech@gmail.com>> wrote:

    Hi,
                 I have deletion script to delete particular cust from
    database.I was saved a file name with custde.sql.i need to run from
    command line like putty tool..But have a problem

    example :

    delete from part where part.custid=$1;

    when i ran custde.sql from putty tool

    db=#\i custde.sql

    it's return error
      psql:custde.sql:9: ERROR:  there is no parameter $1
    LINE 1: ..._ID from part where  part.CUST_ID = $1);

    please let me know how to run script ..and how to give parameter..

--
Adrian Klaver
adrian.klaver@aklaver.com

On 8/27/2014 11:59 AM, Ramesh T wrote:
>
> In oracle I ran the deletion script to clean up the particular
> database using custid.custid is the parameter .&&1 is used in the
> oracle Inthe same way tried but $1 not possible ?but using function is
> possible is their any problem with fun to Clean the database I have
> 100 statements in script ...

where do you expect this $1 value to come from?



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 08/27/2014 11:59 AM, Ramesh T wrote:
>
> In oracle I ran the deletion script to clean up the particular database
> using custid.custid is the parameter .&&1 is used in the oracle Inthe
> same way tried but $1 not possible ?but using function is possible is
> their any problem with fun to Clean the database I have 100 statements
> in script ...

First as has been pointed out before Oracle != Postgres.

Also as been pointed out before you have two options:

1) Pay for the EDB Postgres Advanced Sever + and get the Oracle
compatibility built ins.

2) Spend the time to convert your Oracle code.

As always it comes down to that magic combination of time and/or money.

Second I do not understand what you are trying to say above. Maybe if
you showed some actual code it would help.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/28/2014 04:22 AM, Ramesh T wrote:
> OK.. i created a function for delete customer from different tables in
> single database.
>
> i want rollback..
>
> my question:
>                         where i need to place rollback ,with in a
> function along with deletion statements..? or after run the function ..?
> i do not need commit..

If you want to invoke an explicit ROLLBACK then you will need an
explicit BEGIN, which means you will need an explicit COMMIT to have the
operation succeed.

For the reasons why see here:

http://www.postgresql.org/docs/9.3/static/tutorial-transactions.html

> please let me know..

First, the function code would be nice to see or at least a heads up on
what language you are using.

Second, there are no transactions inside functions. In the case of
plpgsql you can use EXCEPTIONs to mimic the behavior:

http://www.postgresql.org/docs/9.3/static/plpgsql-structure.html

"t is important not to confuse the use of BEGIN/END for grouping
statements in PL/pgSQL with the similarly-named SQL commands for
transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do
not start or end a transaction. Functions and trigger procedures are
always executed within a transaction established by an outer query —
they cannot start or commit that transaction, since there would be no
context for them to execute in. However, a block containing an EXCEPTION
clause effectively forms a subtransaction that can be rolled back
without affecting the outer transaction. For more about that see Section
40.6.6."

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

>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/28/2014 04:22 AM, Ramesh T wrote:
> OK.. i created a function for delete customer from different tables in
> single database.
>
> i want rollback..
>
> my question:
>                         where i need to place rollback ,with in a
> function along with deletion statements..? or after run the function ..?
> i do not need commit..

Realized in my last post the below was not entirely clear:

"If you want to invoke an explicit ROLLBACK then you will need an
explicit BEGIN, which means you will need an explicit COMMIT to have the
operation succeed. "

should have been:

If you want to invoke an explicit ROLLBACK when you have a error then
you will need an explicit BEGIN, which means you will need an explicit
COMMIT when the operation succeeds in order for the transaction to
complete.

> please let me know..

--
Adrian Klaver
adrian.klaver@aklaver.com