Thread: Function for retreiving datatype
Does postgres have a function to determine the data type of an argument? I'm looking for something analogous to PHP's gettype function. I had a look through the documentation and did a few likely pattern searches with \df. Nothing came up. Apologies in advance if the answer is obvious. Cheers BJ
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? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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.
On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote: > > 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. Where would you call this gettype() function from? It seems like you have a chicken-and-egg situation: you need to provide a column definition list when you issue the query, but you don't know what the return row will look like until the query executes the function. In the current implementation, if a function returns SETOF RECORD then you need to know in advance what columns a particular invocation of that function will return. > 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. Consider suggesting that to the developers. I'm not sure what the best list would be -- maybe pgsql-bugs if you consider the terse message to be a bug, or maybe pgsql-hackers since it's a proposed enhancement. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Example :
> psql
create table test (id serial primary key, data10 varchar(10), data20
varchar(20), data text );
insert into test (data10, data20, data) values ('ten','twenty','all i
want');
> python
import psycopg
db = psycopg.connect("host=localhost dbname=.....")
c = db.cursor()
c.execute( "SELECT * FROM test LIMIT 1;" )
print c.description
(('id', 23, None, 4, None, None, None), ('data10', 1043, None, 10, None,
None, None), ('data20', 1043, None, 20, None, None, None), ('data', 25,
None, -1, None, None, None))
Here the integer behind the name is the type-id, the next one which is not
None is the length.
Lets paste the typids in postgres :
=> select typname,typelem from pg_type where typelem in (23,25,1043);
typname | typelem
----------+---------
_int4 | 23
_text | 25
_varchar | 1043
Using this you can easily print the types returned by whatever :
> python
c.execute('rollback')
c.execute( "SELECT typelem,typname FROM pg_type WHERE typelem != 0" )
typmap = dict(c.fetchall())
c.execute( "SELECT * FROM test LIMIT 1;" )
print "\n".join(["%s\t: %s\t%d" % (field_name, typmap[typid], typlen) for
field_name,typid,_,typlen,_,_,_ in c.description])
id : _int4 4
data10 : _varchar 10
data20 : _varchar 20
data : _text -1
c.dictfetchall()
[{'data20': 'twenty', 'data': 'all i want', 'id': 1, 'data10': 'ten'}]
Don't ask me what the remaining things returned in c.description are, I
don't know. Read the docs.
Michael Fuhr wrote: <blockquote cite="mid20050110191604.GA5387@winnie.fuhr.org" type="cite"><pre wrap="">On Tue, Jan 11,2005 at 05:26:59AM +1100, Brendan Jurd wrote: </pre><blockquote type="cite"><pre wrap="">The original problem had to dowith 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. </pre></blockquote><pre wrap=""> Where would you call this gettype() function from? It seems like you have a chicken-and-egg situation: you need to provide a column definition list when you issue the query, but you don't know what the return row will look like until the query executes the function. In the current implementation, if a function returns SETOF RECORD then you need to know in advance what columns a particular invocation of that function will return. </pre></blockquote> Not really an issue. I could have yanked the source query out of the row-returning function, plantedit into a regular console, and wrapped the hypothetical gettype() function around the individual columns to test thetype of their output.<br /><br /> But that's getting away from the point. It doesn't really matter whether I could haveused gettype() to solve that particular problem. Which is why I didn't bring it up in my original post. My post wasall about finding out whether postgres has this functionality. If it does, and I just wasn't looking hard enough, it'sall good. If it doesn't, I'd like to explore the possibility of getting it added in.<br /><br /><blockquote cite="mid20050110191604.GA5387@winnie.fuhr.org"type="cite"><pre wrap=""></pre><blockquote type="cite"><pre wrap="">On thatnote, 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. </pre></blockquote><pre wrap=""> Consider suggesting that to the developers. I'm not sure what the best list would be -- maybe pgsql-bugs if you consider the terse message to be a bug, or maybe pgsql-hackers since it's a proposed enhancement. </pre></blockquote> hackers seems like the place to go then -- I definitely don't consider it a bug.<br /><br /> ThanksMichael<br /><br /> BJ<br />
Pierre-Frédéric Caillaud wrote:
>
> Example :
>
>> psql
>
> create table test (id serial primary key, data10 varchar(10), data20
> varchar(20), data text );
> insert into test (data10, data20, data) values ('ten','twenty','all i
> want');
>
>> python
>
<snip>
I know that these kinds of functions are available from other languages,
I was after an internal postgres function. Sorry if I wasn't clear
about that.
Michael Fuhr wrote: >On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote: > > >>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. >> >> > >Where would you call this gettype() function from? It seems like >you have a chicken-and-egg situation: you need to provide a column >definition list when you issue the query, but you don't know what >the return row will look like until the query executes the function. >In the current implementation, if a function returns SETOF RECORD >then you need to know in advance what columns a particular invocation >of that function will return. > > > Not really an issue. I could have yanked the source query out of the row-returning function, planted it into a regular console, and wrapped the hypothetical gettype() function around the individual columns to test the type of their output. But that's getting away from the point. It doesn't really matter whether I could have used gettype() to solve that particular problem. Which is why I didn't bring it up in my original post. My post was all about finding out whether postgres has this functionality. If it does, and I just wasn't looking hard enough, it's all good. If it doesn't, I'd like to explore the possibility of getting it added in. >>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. >> >> > >Consider suggesting that to the developers. I'm not sure what the >best list would be -- maybe pgsql-bugs if you consider the terse >message to be a bug, or maybe pgsql-hackers since it's a proposed >enhancement. > > > hackers seems like the place to go then -- I consider it an RFE rather than a bug. Thanks Michael BJ
On Tue, Jan 11, 2005 at 08:29:33AM +1100, Brendan Jurd wrote: > My post was all about finding out whether postgres has this > functionality. Certainly PostgreSQL provides a way to discover a row's column types, but how to do it depends on where you're trying to do it from. If you're writing a client program in C using libpq, you could use the functions documented under "Retrieving Query Result Information" in the "Command Execution Functions" section of the libpq chapter of the documentation. If you're writing a client program using ECPG then you could use a descriptor area. If you're writing a server-side C program that makes queries via SPI then you could use the functions defined under "Interface Support Functions" in the "Server Programming Interface" chapter. Is that what you're looking for? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote: >On Tue, Jan 11, 2005 at 08:29:33AM +1100, Brendan Jurd wrote: > > > >>My post was all about finding out whether postgres has this >>functionality. >> >> > >Certainly PostgreSQL provides a way to discover a row's column >types, but how to do it depends on where you're trying to do it >from. If you're writing a client program in C using libpq, you >could use the functions documented under "Retrieving Query Result >Information" in the "Command Execution Functions" section of the >libpq chapter of the documentation. If you're writing a client >program using ECPG then you could use a descriptor area. If you're >writing a server-side C program that makes queries via SPI then you >could use the functions defined under "Interface Support Functions" >in the "Server Programming Interface" chapter. > >Is that what you're looking for? > > > Actually I'm looking for an internal function -- something within postgres' implementation of SQL itself, which I can use in queries independent of the front-end language. The same way you use functions like to_char() or now().
On Tue, Jan 11, 2005 at 09:31:26AM +1100, Brendan Jurd wrote:
> Actually I'm looking for an internal function -- something within
> postgres' implementation of SQL itself, which I can use in queries
> independent of the front-end language. The same way you use functions
> like to_char() or now().
Can you provide a hypothetical example of how you'd use this function?
The problem is still vague enough to have different solutions
depending on what you're trying to do. For example, if you want
to know the column types of a table then you could query the system
catalogs; but if you want to know the type of an arbitrary column
of an arbitrarily complex query then I'm not aware of a way to get
it in SQL (that doesn't mean there isn't a way, it just means that
I don't know of a way). You could, however, write a C function
that takes an "anyelement" argument and returns its type's OID.
Here's an example using a trivial coltype() function that I just
wrote:
SELECT coltype(1) AS typeoid, coltype(1)::regtype AS typename;
typeoid | typename
---------+----------
23 | integer
(1 row)
SELECT coltype(1.234) AS typeoid, coltype(1.234)::regtype AS typename;
typeoid | typename
---------+----------
1700 | numeric
(1 row)
CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT, birthday DATE);
INSERT INTO foo (name, birthday) VALUES ('Johnny', '2005-01-02');
SELECT id, coltype(id)::regtype AS idtype,
name, coltype(name)::regtype AS nametype,
birthday, coltype(birthday)::regtype AS birthdaytype
FROM foo;
id | idtype | name | nametype | birthday | birthdaytype
----+---------+--------+----------+------------+--------------
1 | integer | Johnny | text | 2005-01-02 | date
(1 row)
Is that anything like what you want? If not, then please be more
specific about a particular problem you're trying to solve.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote:
>On Tue, Jan 11, 2005 at 09:31:26AM +1100, Brendan Jurd wrote:
>
>
>
>>Actually I'm looking for an internal function -- something within
>>postgres' implementation of SQL itself, which I can use in queries
>>independent of the front-end language. The same way you use functions
>>like to_char() or now().
>>
>>
>
>Can you provide a hypothetical example of how you'd use this function?
>The problem is still vague enough to have different solutions
>depending on what you're trying to do. For example, if you want
>to know the column types of a table then you could query the system
>catalogs; but if you want to know the type of an arbitrary column
>of an arbitrarily complex query then I'm not aware of a way to get
>it in SQL (that doesn't mean there isn't a way, it just means that
>I don't know of a way). You could, however, write a C function
>that takes an "anyelement" argument and returns its type's OID.
>Here's an example using a trivial coltype() function that I just
>wrote:
>
>SELECT coltype(1) AS typeoid, coltype(1)::regtype AS typename;
> typeoid | typename
>---------+----------
> 23 | integer
>(1 row)
>
>SELECT coltype(1.234) AS typeoid, coltype(1.234)::regtype AS typename;
> typeoid | typename
>---------+----------
> 1700 | numeric
>(1 row)
>
>CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT, birthday DATE);
>INSERT INTO foo (name, birthday) VALUES ('Johnny', '2005-01-02');
>SELECT id, coltype(id)::regtype AS idtype,
> name, coltype(name)::regtype AS nametype,
> birthday, coltype(birthday)::regtype AS birthdaytype
>FROM foo;
> id | idtype | name | nametype | birthday | birthdaytype
>----+---------+--------+----------+------------+--------------
> 1 | integer | Johnny | text | 2005-01-02 | date
>(1 row)
>
>Is that anything like what you want? If not, then please be more
>specific about a particular problem you're trying to solve.
>
>
>
Your coltype() function is exactly what I'm looking for. I'd envisaged
something that takes an anyelement argument and returns the type as
text, but returning the OID is even better.
Can you please provide the source for the function?
On Tue, Jan 11, 2005 at 11:00:15AM +1100, Brendan Jurd wrote:
> Your coltype() function is exactly what I'm looking for. I'd envisaged
> something that takes an anyelement argument and returns the type as
> text, but returning the OID is even better.
>
> Can you please provide the source for the function?
Here's the C code:
#include "postgres.h"
#include "fmgr.h"
Datum coltype(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(coltype);
Datum
coltype(PG_FUNCTION_ARGS)
{
PG_RETURN_OID(get_fn_expr_argtype(fcinfo->flinfo, 0));
}
I lifted the expression to get the argument's type from "Polymorphic
Arguments and Return Types" in the "C-Language Functions" section
of the "Extending SQL" chapter in the documentation. Read "Compiling
and Linking Dynamically-Loaded Functions" for build instructions.
After you've built and installed the shared object file, create the
function with the following SQL statement:
CREATE OR REPLACE FUNCTION coltype(anyelement) RETURNS oid
AS '$libdir/coltype' LANGUAGE C IMMUTABLE;
Change '$libdir/coltype' if you name the shared object file something
other than coltype.so.
Now watch, somebody will jump in and say, "Why'd you go to all that
trouble? Here's an easier way...."
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote:
> On Tue, Jan 11, 2005 at 11:00:15AM +1100, Brendan Jurd wrote:
>
>>Your coltype() function is exactly what I'm looking for. I'd envisaged
>>something that takes an anyelement argument and returns the type as
>>text, but returning the OID is even better.
[...snip slick function...]
> Now watch, somebody will jump in and say, "Why'd you go to all that
> trouble? Here's an easier way...."
Not exactly a drop in replacement, but you could check whether you have
one of set of types with the undocumented* IS OF construct:
regression=# select prosrc is of (text) from pg_proc limit 1;
?column?
----------
t
(1 row)
regression=# select prosrc is of (bytea) from pg_proc limit 1;
?column?
----------
f
(1 row)
regression=# select prosrc is of (bytea,text) from pg_proc limit 1;
?column?
----------
t
(1 row)
Also note that in PL/pgSQL, you can use %TYPE to create a variable to
the same type as an argument:
"%TYPE is particularly valuable in polymorphic functions, since the
data types needed for internal variables may change from one call to
the next. Appropriate variables can be created by applying %TYPE to
the function's arguments or result placeholders."
http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE
And a variable can also be created with the function's runtime-resolved
return type:
"When the return type of a PL/pgSQL function is declared as a
polymorphic type (anyelement or anyarray), a special parameter $0 is
created. Its data type is the actual return type of the function, as
deduced from the actual input types (see Section 33.2.5). This allows
the function to access its actual return type as shown in Section
37.4.2. $0 is initialized to null and can be modified by the
function, so it can be used to hold the return value if desired,
though that is not required. $0 can also be given an alias."
http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
Joe
* search the archives if you want the background as to why it is still
undocumented -- in short, it is close to, but not quite SQL99 compliant,
and although I had hoped to fix that "not quite" part, I've yet to find
the time :(
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@blakjak.sytes.net> wrote in message
news:41E2C8F3.7090504@blakjak.sytes.net...
> 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
>