Re: Queyring for columns which are exist in table.

Sim Zacks <sim@xxxxxxxxxxxxxx> · Thu, 27 Jan 2011 16:15:37 +0200



    What you will need
      to do in your function is 
    

    ...
    sql = 'select ' || checkColumn('tbl','c2','0')
        || '::text as fld from tbl';

      
    for row in execute sql loop

      
        return next row.fld;

      
    end loop;

      
    return;

      
    ...

      
    where the function returns a set
        of text (or int or whatever the datatype will always be.

    
    Sim

    
    On 01/27/2011 01:35
      PM, Santosh Bhujbal (sabhujba) wrote:
    
      
        Hi Sim,
         
        Thank you
            for the response.
         
        My
            question is why is it not working?
        What
            changes needs to be done in function or in calling SQL to
            make it work as per requirement?
         
        Thanks,
        Santosh.
         
        
            From: pgsql-general-owner@xxxxxxxxxxxxxx
                [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On
                  Behalf Of Sim Zacks

                Sent: Thursday, January 27, 2011 4:26 PM

                To: pgsql-general@xxxxxxxxxxxxxx

                Subject: Re:  Queyring for columns which
                are exist in table.
          
        
        On 01/27/2011 09:52 AM,
          Santosh
          Bhujbal (sabhujba) wrote:
        
          CREATE
              OR REPLACE FUNCTION ColumnAlreadyExists(name, name)
              RETURNS INTEGER AS E' 
          DECLARE
              columnCount INTEGER;
          BEGIN
           
          SELECT
              COUNT (pg_attribute.attname) into columnCount FROM
              pg_attribute,pg_class,
              pg_type WHERE ((pg_attribute.attrelid=pg_class.oid) AND
              (pg_attribute.atttypid=pg_type.oid) AND (pg_class.relname
              = $1) AND
              (pg_attribute.attname = $2));
           
          IF
              columnCount = 0 THEN
          RETURN
              0;
          END
              IF;
          RETURN
              1;
          END;
          '
              LANGUAGE 'plpgsql';
           
           
          DROP
              FUNCTION checkColumn(name,name,name);
          CREATE
              OR REPLACE FUNCTION checkColumn(name, name, name) RETURNS
              name AS E'
          DECLARE
              isColumnExist INTEGER;
           
          BEGIN
          SELECT
              ColumnAlreadyExists ($1,$2) into isColumnExist;
           
          IF
              isColumnExist = 0 THEN
          RETURN
              name($3);
          ELSE
          RETURN
              name($2);
          END
              IF;
          END;
          '
              LANGUAGE 'plpgsql';
           
           
          Function
              checkColumn should return proper column name (second
              parameter) if column exist
              and third parameter if column not exist.
           
          NOW
              when I try to execute following command it returns
              improper result.
          I
              expect proper column values as a output of query.
           
          SELECT(checkColumn('tbl','c2','0'))::name
              FROM tbl;
           
          mydb=#
              SELECT (checkColumn('tbl','c2','0'))::name FROM tbl;
           checkcolumn
          -------------
           c2
           c2
           c2
           c2
           c2
           c2
           c2
           c2
          (8
              rows)
           
          mydb=#
           
           
          Above
              query should return actual values present for c2 column in
              tbl.
          But
              it’s not working as desired.
           
          Please
              help me in this.
           
          Thanks
              in advance,
          Santosh.
        
        the name datatype won't cast
            it into a
            selectable column.

            Forgetting about your function, if you try select 'c2'::name
            from tbl it will
            also return c2 as a name datatype.

            What you want to do is a dynamic query where you use execute
            or for row in
            execute in a plpgsql function.

            
            Also to see if the column exists, I would recommend using
            information_schema.columns