COPY use in function with variable file name

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

Responses

Browse pgsql-sql by date

  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