Record type and ROW type in pl/pgsql functions

From: dinar(at)yantel(dot)ru
To: pgsql-bugs(at)postgresql(dot)org
Subject: Record type and ROW type in pl/pgsql functions
Date: 2004-04-20 04:55:38
Message-ID: 431224610.20040420085538@yantel.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,
I'm runninig latest version (7.4.2) of postgresql on OpenBSD 3.4.

I created table

CREATE TABLE "billing"."mail_relayhosts" (
"id" SERIAL,
"hosts" CIDR,
"description" VARCHAR(255),
CONSTRAINT "mail_relayhosts_pkey" PRIMARY KEY("id")
);

and made pl/pgsql function:

CREATE OR REPLACE FUNCTION "billing"."mail_get_relayhosts" () RETURNS varchar AS'
declare
hosts_str mail_relayhosts%ROWTYPE;
hst varchar(256);
begin
hst:=''127.0.0.1'';
FOR hosts_str IN SELECT hosts from mail_relayhosts LOOP
hst:=hst||'':''||text(hosts_str.hosts);
END LOOP;
return hst::varchar;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

when I call this finction I get error:
ERROR: invalid input syntax for integer: "217.23.184.0/24"
Actually there is a record in mail_relayhosts.

but if make function like this:

CREATE OR REPLACE FUNCTION "billing"."mail_get_relayhosts" () RETURNS varchar AS'
declare
hosts_str RECORD;
hst varchar(256);
begin
hst:=''127.0.0.1'';
FOR hosts_str IN SELECT hosts from mail_relayhosts LOOP
hst:=hst||'':''||text(hosts_str.hosts);
END LOOP;
return hst::varchar;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

everything works fine. Is there any difference between rowtype and
record?
I understand rowtype has predefined structure, that record
has not.
But by manual description in Looping Through Query Results
as variables can be taken both of them:
FOR record_or_row IN query LOOP
statements
END LOOP;

But in practice only record type can be taken.
Is it bug or I've something missed?

Best regards, Dinar Talipov

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-04-20 12:42:17 Re: Record type and ROW type in pl/pgsql functions
Previous Message Bruce Momjian 2004-04-20 03:46:57 Re: [BUGS] Bug in pg_autovacuum ?