[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    [SQL] COPY use in function with variable file name
From:       Sondaar Roelof <roelof.sondaar () scania ! com>
Date:       2003-01-29 8:05:52
[Download RAW message or body]

Hello,

I can't figure out how to make this work, or is not possible?

In a function i would like to read a file.
The file name is determined by a value from a table.
However the COPY statement does not to accept this?
I tried various forms of adding (single)-quotes but no luck.

Anyone any ideas?

Function:
CREATE FUNCTION dnsdhcp_dns_raw()
/* Fill table dns_raw with dns data */
RETURNS integer AS '
DECLARE
    r           RECORD;
    ntw         TEXT;
BEGIN
    /* Do for all domain names */
    FOR r IN SELECT domain FROM network
        WHERE position(''n'' IN use) > 0 and ipaddress != ''127.0.0.0/24''
LOOP
        ntw := ''/tmp/db.'' || r.domain;
        DELETE FROM dns_raw; /* Clear table */
RAISE NOTICE ''Network: %'', ntw;
        COPY dns_raw FROM ntw DELIMITERS ''~''; /* Get the data */
    END LOOP;
    RETURN 0;
END;'
LANGUAGE 'plpgsql';

Result:
id=# select dnsdhcp_dns_raw();
NOTICE:  Network: /tmp/db.test.dummy.com
ERROR:  parser: parse error at or near "$1"

Tables:
CREATE TABLE dns_raw (
    data                TEXT
);

                                  Table "network"
     Attribute      |  Type   |                      Modifier
--------------------+---------+---------------------------------------------
-------
 id                 | integer | not null default
nextval('"network_id_seq"'::text)
 ipaddress          | cidr    | not null
 domain             | text    | not null
 email              | text    | not null
 location           | text    | not null default 'l'
 use                | text    | not null default 's'
 ttl                | text    | not null default '3h'
 serial             | integer | not null default 1
 refresh            | text    | not null default '3h'
 retry              | text    | not null default '1h'
 expire             | text    | not null default '1w'
 cachettl           | text    | not null default '1d'
 lease_time_default | integer | not null default 86400
 lease_time_minimum | integer | not null default 0
 lease_time_maximum | integer | not null default 0
 client_updates     | text    | not null default 'ignore'
 ddns_update_style  | text    | not null default 'interim'
 description        | text    |

Best regards,
Roelof

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
[prev in list] [next in list] [prev in thread] [next in thread]