Record type and ROW type in pl/pgsql functions
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
dinar@yantel.ru writes:
declare
hosts_str mail_relayhosts%ROWTYPE;
...
FOR hosts_str IN SELECT hosts from mail_relayhosts LOOP
You would need SELECT * FROM here, if you're selecting the whole row.
This is going to try to stuff "hosts" into the first column of the
rowtype variable, which is the wrong datatype. (I think it would then
complain about too few columns supplied, but it's not getting that far.)
regards, tom lane