PostgreSQL COPY - using variable filename?

[QuercusDK]

Hi all

I would like to create a function that copies CDR-records from text files. This should be done every hour, so I want my function to look something like this:

CREATE OR REPLACE FUNCTION cdr_copy( ) RETURNS VOID AS $$
DECLARE
mytime TIMESTAMP := timeofday( );
cdr_path CHAR(50) := '/home/cdr/';
cdr_file CHAR(50) := cdr_path + myime;
BEGIN
...
...
COPY mytable FROM $cdr_file DELIMITER AS ' ';

Since COPY don't take a variable, but only do relative path, then what to do?
Any suggestions?

Regards,
Henrik

 

[feherke]

Hi

This works for me in PostgreSQL 7.3.3. Try to adapt it to your needs :

[b]create or replace function[/b] [green][i]loadfile[/i][/green]()
[b]returns void as[/b] ' 
declare 
  [green][i]filename[/i][/green] varchar:=\'/path/to/datafile.txt\'; 
begin 
  execute \'copy [green][i]tablename[/i][/green] from \'\'\' || [green][i]filename[/i][/green] || \'\'\'\'; 
  return; 
end; 
' [b]language[/b] plpgsql;

Feherke.

http://rootshell.be/~feherke/