How to find column type

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: How to find column type
Date: 2010-01-12 14:27:21
Message-ID: E3A11A35E9434E2B9F3C13C9040DA517@andrusnotebook
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm looking for a way to create function which returns column type as single
character:

C = char/text/varchar, N=numeric, L=bool, .... others, U=unknown

from expression in form 'tablename.columnname', where tablename if table
from
current search_path tables. search_path has two schemas. First is custom
schema which can be different for different runs.
second is public schema always

ExpressionType('mytable.mycol') retuns C if mytable has column mycol
of type CHAR(10)

Currently I'm using hard coded column names as shown below.
User can add its own columns to db so hard-coding is not accurate.

How to implement such generic function ?

Andrus.

CREATE OR REPLACE FUNCTION public.ExpressionType(expression text )
RETURNS char(1) AS
$BODY$
BEGIN
IF LOWER(expression)='isik.sotskorrkl' THEN
RETURN 'L';
END IF;
RETURN 'U';
END;
$BODY$ language plpgsql immutable;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Grzegorz Jaśkiewicz 2010-01-12 14:30:13 Re: How to find column type
Previous Message Sergey Levchenko 2010-01-12 14:26:18 Re: need help with query, how to fold select result to array?