From: | Sondaar Roelof <roelof(dot)sondaar(at)scania(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | COPY use in function with variable file name |
Date: | 2003-01-29 08:05:52 |
Message-ID: | B659C5A6EF67D5119430006097487147329946@sv7007b.nl.scania.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sondaar Roelof | 2003-01-29 08:16:23 | Re: Creating tables from within functions |
Previous Message | Abdul Wahab Dahalan | 2003-01-29 07:27:18 | USING INDEX |