Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

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

by erix (Prior)
on Apr 05, 2014 at 17:51 UTC ( [id://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

).

Replies are listed 'Best First'.
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?

        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);
        A cavall donat, no li miris el dentat.
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!

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2024-03-29 09:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found