Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Unanswered: function compilation in postgresql

    Hello All,
    I am brand new to posgresql and trying to write a small funtion.I am trying to execute this below function using data gainity and it throws me an error

    Please help me figure this one out.
    ERROR: 42601: syntax error at or near "DECLARE"

    Code:
    create or replace function alter_st( integer, integer)
    RETURNS text AS

    DECLARE
    vtableAlterddl text;
    columnrecord record;
    v_max integer;

    BEGIN

    select max(ordinalposition) into vmax from informationschema.columns where tablename='basicplcy'
    and tableschema='public'
    FOR column_record IN

    (select b.ordinalposition as ordinalposition ,a.tableschema as tableschema,a.tablename as tablename,a.columnname as columnname,b.datatype as datatype,CASE WHEN b.datatype in ('timestamp without time zone','numeric')THEN coalesce((b.charactermaximumlength::text),'')||'' ELSE '('|| coalesce((b.charactermaximumlength::text),'')||')' END
    from
    (select * from informationschema.columns
    where tablename='stgbasicplcy'
    and tableschema='public'
    order by ordinal_position) a

    join

    (select * from informationschema.columns
    where tablename='basicplcy'
    and tableschema='public'
    order by ordinal_position) b

    on a.columnname=b.columnname order by b.ordinal_position)

    LOOP
    IF columnrecord.ordinalposition = 1 THEN
    vtableAlterddl:='ALTER TABLE '||columnrecord.schemaname||'.'||columnrecord.tabl ename||' (';
    ELSE
    vtableAlterddl:=vtableAlter_ddl ||',';
    END IF;

    IF columnrecord.ordinalposition <= vmax THEN
    vtableAlterddl:= vtableAlterddl||chr(10)||
    ' '||columnrecord.columnname||' '||columnrecord.columntype||' '||columnrecord.columndefaultvalue||' '||columnrecord.columnnot_null;
    END IF;
    END LOOP;

    v_table_Alter_ddl:=v_table_Alter_ddl||');';
    RETURN v_table_Alter_ddl;
    END;

    LANGUAGE sql IMMUTABLE STRICT;

    Thanks

  2. #2
    Join Date
    Dec 2012
    Posts
    43
    function body must encapsulated between delimiters (for example "'" or "$$")

    Example function (copied from PotsgreSQL documentation):

    Code:
    CREATE FUNCTION add(integer, integer) RETURNS integer
        AS 'select $1 + $2;'
        LANGUAGE SQL
        IMMUTABLE
        RETURNS NULL ON NULL INPUT;

  3. #3
    Join Date
    Dec 2012
    Posts
    43
    And also I believe that you should replace
    Code:
    LANGUAGE sql IMMUTABLE STRICT;
    by
    Code:
    LANGUAGE plpgsql IMMUTABLE STRICT;
    because it's not only SQL.

Posting Permissions

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