Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2012
    Posts
    30

    Unanswered: How to Concatenate String With Variable?

    Hi to All,

    I am working on this function

    Code:
    create or replace function update_the_db() returns void as
    $$
    declare
    anno_nr integer;
    
    begin
    anno_nr := extract(year from current_date);
    
    
    for i in anno_nr..2009 loop
        if not exists(select * from information_schema.tables 
            where 
                table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA
                and table_name = anno||anno_nr::text||
                ) then
    
            create table anno||anno_nr::text ||;
            
            (
                the_id int not null,
                name text
            );
    
        end if;
        anno_nr := anno_nr -1;
    end loop;
    
    end;
    $$
    language 'plpgsql';
    
    select update_the_db();
    drop function update_the_db();

    But if I execute the function I obtain this message error

    Code:
    ERROR:  syntax error at or near "||"
    LINE 17:         create table anno||anno_nr::text ||;
    Sincerly, I am not able to resolve the problem.

    Could SomeOne resolve it?

    Tanks in advance.
    Gianni Pinna.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You can't specify a table name using a variable. You need to use dynamic SQL to achieve this:

    http://www.postgresql.org/docs/curre...-EXECUTING-DYN
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Dec 2012
    Posts
    30
    Hi Shammat,

    Tank You very much for Your promt reply.

    After many hours of work, I arrived to this solution wich well works.

    The end was :
    Code:
    FROM ACTUAL YEAR TO 2009
            IF THE TABLE DOES NOT EXIST
               CREATE TABLE ANNO(2009-2013)
    The function:

    Code:
    CREATE FUNCTION create_table() RETURNS VOID AS
    $$
    DECLARE
    com text;
    anno_nr integer;
    BEGIN
    anno_nr := extract(year from current_date);
    
    --Dall'anno attuale sino al 2009 esegui i comandi di seguito
    for i in reverse anno_nr..2009 loop
    
    --COMANDI
     BEGIN
      --COMANDO 1 Produci una table ... 
        com := 'CREATE TABLE anno'||anno_nr::text||
        '(my_column INT)';
      execute com;  
      -- ... Se gia' non esiste; se esiste non farealcunche' ed termina questo comando 
     EXCEPTION WHEN duplicate_table THEN
     --non fare alcunche' 
     END;
     --EPPOI COMANDO 2
    anno_nr := anno_nr - 1;
    end loop;
    END;
    $$
    LANGUAGE plpgsql;
    
    select create_table();
    drop function create_table();
    Hope well for You, Kind Shammat.
    Best Rgrds

    Gianni Pinna.

Posting Permissions

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