Re: Function for retreiving datatype

Brendan,

I have had similar problems and the way I resolve it is by running the SQL
statement directly in PGAdmin and in the resultset it tells you what the
field types are.

For example.

create or replace function test1(vara int, varb int) returns setof record as
$$
declare row record;
begin
for row in select * from table1 where field1=vara and field2=varb LOOP
return next row;
end loop;
return;
end;
$$ language 'plpgsql'

I copy the select statement and either make up variables for vara and varb
or completely leave the where statement out. The result set then has:
field1 (int) field2(varchar).....

I don't see how a function would help you in the middle of the code because
you need to already know the field type before you call the function. Also
the fieldtype can dynamically change if you are concatenating or applying
other functions to the fields.
For example, field xyz as a varchar and abc as text. xyz || abc stores the
result as a text.

Good Luck
Sim

"Brendan Jurd" <blakjak(at)blakjak(dot)sytes(dot)net> wrote in message
news:41E2C8F3(dot)7090504(at)blakjak(dot)sytes(dot)net(dot)(dot)(dot)
> Michael Fuhr wrote:
>
> >On Tue, Jan 11, 2005 at 03:28:08AM +1100, Brendan Jurd wrote:
> >
> >
> >
> >>Does postgres have a function to determine the data type of an
> >>argument?
> >>
> >>
> >
> >In what context? What problem are you trying to solve?
> >
> >
> >
> Well, I solved the original problem in a different way, but I'd still
> like to know whether such a function exists.
>
> The original problem had to do with querying a row-returning function.
> I had an SQL function that returned "SETOF record", and I was trying to
> use it in the FROM clause of a query. To do so, you need to provide a
> list of column definitions. I was getting the error about the returned
> row types not matching my column defs. In the end it was a simple
> mistake -- I had specified 'text' where I should have specified
> 'varchar'. I had thought to use some kind of "gettype" function to find
> out exactly what data types my query was returning.
>
> On that note, it might be helpful to increase the verbosity of the
> "returned row types" error message, so that it actually explains the
> mismatch it encountered. Something like "Returned column 3 is
> varchar(15) but column definition is text" would have made debugging a
> whole lot easier.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>