Professional Web Applications Themes

Function for retreiving datatype - PostgreSQL / PGSQL

Does postgres have a function to determine the data type of an argument? I'm looking for something ogous to PHP's gettype function. I had a look through the doentation and did a few likely pattern searches with \df. Nothing came up. Apologies in advance if the answer is obvious. Cheers BJ ---------------------------(end of broadcast)--------------------------- TIP 8: explain yze is your friend...

  1. #1

    Default Function for retreiving datatype

    Does postgres have a function to determine the data type of an
    argument? I'm looking for something ogous to PHP's gettype
    function. I had a look through the doentation and did a few likely
    pattern searches with \df. Nothing came up.

    Apologies in advance if the answer is obvious.

    Cheers

    BJ

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Brendan Jurd Guest

  2. #2

    Default Re: Function for retreiving datatype

    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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Michael Fuhr Guest

  3. #3

    Default Re: Function for retreiving datatype

    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?

    [url]http://archives.postgresql.org[/url]

    Brendan Jurd Guest

  4. #4

    Default Re: Function for retreiving datatype

    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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Michael Fuhr Guest

  5. #5

    Default Re: Function for retreiving datatype


    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.

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Pierre-Frédéric Caillaud Guest

  6. #6

    Default Re: Function for retreiving datatype

    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.

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Brendan Jurd Guest

  7. #7

    Default Re: Function for retreiving datatype

    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

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Brendan Jurd Guest

  8. #8

    Default Re: Function for retreiving datatype

    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 doented under "Retrieving Query Result
    Information" in the "Command Execution Functions" section of the
    libpq chapter of the doentation. 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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Michael Fuhr Guest

  9. #9

    Default Re: Function for retreiving datatype

    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 doented under "Retrieving Query Result
    >Information" in the "Command Execution Functions" section of the
    >libpq chapter of the doentation. 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().

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Brendan Jurd Guest

  10. #10

    Default Re: Function for retreiving datatype

    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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Michael Fuhr Guest

  11. #11

    Default Re: Function for retreiving datatype

    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?

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Brendan Jurd Guest

  12. #12

    Default Re: Function for retreiving datatype

    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 doentation. 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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Michael Fuhr Guest

  13. #13

    Default Re: Function for retreiving datatype

    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 undoented* 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."

    [url]http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE[/url]

    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."

    [url]http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES[/url]


    Joe

    * search the archives if you want the background as to why it is still
    undoented -- 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 :(


    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Joe Conway Guest

  14. #14

    Default 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" <blakjakblakjak.sytes.net> wrote in message
    news:41E2C8F3.7090504blakjak.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?
    >
    > [url]http://archives.postgresql.org[/url]
    >

    Sim Zacks Guest

Similar Threads

  1. Replies: 0
    Last Post: March 14th, 04:18 PM
  2. Inserting, updating, and retreiving a file as blob
    By MindHunter in forum Coldfusion Database Access
    Replies: 1
    Last Post: September 6th, 05:34 PM
  3. ASP.NET Insert into bit datatype
    By RajDogLives in forum Adobe Dreamweaver & Contribute
    Replies: 9
    Last Post: May 20th, 04:45 PM
  4. citext datatype
    By Sim Zacks in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 2nd, 12:20 PM
  5. Check datatype
    By Andrew Durstewitz in forum ASP
    Replies: 2
    Last Post: June 30th, 03:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139