Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re^2: Help on selecting and deleting many records in a DB.

by erix (Vicar)
on Apr 05, 2014 at 17:51 UTC ( #1081271=note: print w/ replies, xml ) Need Help??


in reply to Re: Help on selecting and deleting many records in a DB.
in thread Help on selecting and deleting many records in a DB.

Can you explain? I don't understand why "a SQL function" would do anything to make this faster, or even easier in any way.

(Btw, in postgres, "SQL function" normally means a function written in SQL, i.e. a PL/SQL function [1] (as opposed to a PL/pgSQL function [2] (or PL/Perl [3], PL/Tcl, PL/Python, etc)).

[0] Server Programming

[1] PL/SQL

[2] PL/pgSQL

[3] PL/Perl

).


Comment on Re^2: Help on selecting and deleting many records in a DB.
Select or Download Code
Re^3: Help on selecting and deleting many records in a DB.
by Anonymous Monk on Apr 05, 2014 at 18:27 UTC
    If you kown exactly what you do and don't have to experiment a little, and love to type long command lines in SQL, sure everything can be done there. It's clear that with your level of awareness, such things don't bother you. There are other reasons - like subsequent functions calls can dispense from nested requests, but I have no time to go in detail. Cheers!
Re^3: Help on selecting and deleting many records in a DB.
by Anonymous Monk on Apr 06, 2014 at 06:56 UTC

    Creating a function for your needs can be as simple as:

    1. Write this code in a file called myfun.test:

    CREATE OR REPLACE PROCEDURAL LANGUAGE plperlu; ALTER PROCEDURAL LANGUAGE plperlu OWNER TO postgres; CREATE OR REPLACE FUNCTION mytest(text) RETURNS text LANGUAGE plperlu AS $_X$ my $data = $_[0]; my $string = $data." -->from mytest\n"; return $string; $_X$;

    (Of course you can have as many arguments and return types you want in your function's definition.)

    2. Insert/replace this function in your db by issuing the command line:

    # \i /path/to/myfun.test

    3. Run the test (given a table called data and a text field called f1):

    # select mytest(f1) from data limit 10

    And that's it... When your function will be ready you can dump/restore it permanently into your db if you want.

      I seems rather more complicated, not easier. In what way does this make the queries faster? Benchmark?

        A cavall donat, no li miris el dentat.
        I would try something like:
        my %ipH; my $sth = spi_query("SELECT * FROM data"); while (defined ($row = spi_fetchrow($sth))) { delete fetched or hashed; }
        but it's not tested.
        And deletion is done by a command like:
        $req = spi_exec_query("DELETE FROM data WHERE id = $id",1);

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1081271]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (7)
As of 2014-08-30 08:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (291 votes), past polls