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

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$;

[download]

(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.

Replies are listed 'Best First'.
Re^4: Help on selecting and deleting many records in a DB.
by Anonymous Monk on Apr 06, 2014 at 07:14 UTC

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

[reply]
      I would try something like: 
      my %ipH;
my $sth = spi_query("SELECT * FROM data");
while (defined ($row = spi_fetchrow($sth))) { 
  delete fetched or hashed;
}

      [download]
      but it's not tested.
[reply]
[d/l]
      And deletion is done by a command like: 
      $req = spi_exec_query("DELETE FROM data WHERE id = $id",1);

      [download]
[reply]
[d/l]
      A cavall donat, no li miris el dentat.
[reply]

In Section Seekers of Perl Wisdom