Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group