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

Re: Function error- A column definition list is required for functions returning "record"

From: "ben sewell" <mosherben(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Function error- A column definition list is required for functions returning "record"
Date: 2006-08-20 16:13:22
Message-ID: bf6c74d80608200913l45e47692ta5f4d0d20e891988@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Tom,
thanks for your reply. Just a follow up question about functions: would it
return the result if some of the parameters are null?

Cheers,
Ben

On 8/18/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "ben sewell" <mosherben(at)gmail(dot)com> writes:
> > I have this error when I tried testing my function. I set the parameters
> to
> > 18,2,,,,,,,, as pandctype but it said this error: A column definition
> list
> > is required for functions returning "record". I got my UDF setup but I
> don't
> > see what the problem is. Can anyone shed any ideas on this?
>
> > create or replace function reports (inreport_id integer,inadviser_id
> integer,inprovider_id integer,inintroducer_id integer,
> > inplangroup_id integer,inplantype_id integer,indatespecific_start
> date,indatespecific_end date,inchild24 date,inchild26 date)
> > returns record as'
>
> You can't simply declare the function as "returns record", because the
> calling query has no idea what to expect (the parser needs to know
> what columns will come out of the function call).  There are basically
> two approaches you can use:
>
> 1. Declare the function as returning a specific rowtype.  Either create
> a named composite type and declare the function as returning that, or
> (if you're using PG 8.1 or later) declare the function with some OUT
> parameters to show what columns it returns.
>
> 2. Declare the set of columns to be returned in the calling query, eg
>
>         SELECT * FROM myfunc(...) AS foo(c1 int, c2 text, ...)
>
> This is what the error message is talking about.  This case is mainly
> meant for special functions like dblink() that are designed to support
> query-determined column lists.
>
> You might want either of these depending on what you're doing, but
> the first is probably simpler.
>
>                         regards, tom lane
>

In response to

pgsql-novice by date

Next:From: Damian CDate: 2006-08-21 01:15:10
Subject: pg_dump output goes to stderr, not stdout?!?
Previous:From: Jasbinder BaliDate: 2006-08-18 21:31:41
Subject: Re: Beginner needs example pg_hba.conf

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