pgsql sql functions sequential execution

Question

If I have these two Postgres function definitions saved in two seperate .sql files:

CREATE OR REPLACE FUNCTION column_exists(tablename text, colname text) RETURNS boolean AS 
        $BODY$
        DECLARE
            q text;
            field_name text;
            onerow record;
        BEGIN
            q = 'SELECT column_name FROM information_schema.columns WHERE table_name='''||tablename||''' AND table_schema =''public''';
            FOR onerow IN EXECUTE q
                LOOP
                   field_name := onerow.column_name;
                   IF ((field_name = colname)) then
                    RETURN true;
                   END IF;                  
                END LOOP;
            RETURN false;
        END;
    $BODY$
LANGUAGE plpgsql
CREATE OR REPLACE FUNCTION correct_col_names() RETURNS VOID AS
    $BODY$
        DECLARE
            q boolean;
        BEGIN                       

             -- rename name column to Name
             select column_exists('National_Parks', 'name') as q;
             IF q = TRUE THEN
            alter table "National_Parks"
            rename column name to "Name";
             END IF;    

             -- remance descriptio column to description
             select column_exists('National_Parks', 'descriptio') as q;
             IF q = TRUE THEN
            alter table "Natioanl_Parks"
            rename column descriptio to "Description";
             END IF;    

        END
    $BODY$
LANGUAGE plpgsql

What is the syntax I need to use to call the sequentially, say in another script? I tried

select correct_col_names()

and this returns the following error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "correct_col_names" line 7 at SQL statement

********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "correct_col_names" line 7 at SQL statement

TIA.

Scott S · Accepted Answer · 2012-12-11 20:00:40Z

3

The problem is that you have SELECT statements that aren't doing anything with the data. Your

select column_exists('National_Parks', 'name') as q;

should be

select column_exists('National_Parks', 'name') INTO q;

The as simply aliases the result as "q" for that query, it doesn't actually put it into the q variable.

Your syntax for calling the functions (select correct_col_names()) is correct for SQL. Once you fix the two errors in that function, it should work.

However, if you were to try select correct_col_names() inside another PL/PGSQL function, you would get the same error, because the select statement isn't actually doing anything with the results. perform correct_col_names() would run without error, because PERFORM is PL/PGSQL syntax for calling something when you don't want to save the result.

edited Dec 11, 2012 at 20:00

answered Dec 11, 2012 at 19:51

Scott S

2,73618 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Klaus Nji Over a year ago

Thanks, that did it. I will also like to call both functions defined in two separate files, from another script. Any ideas on how to do this?

Scott S Over a year ago

There are many ways to call a function, and a comment is not a good medium for a complete answer. Have a look here - postgresql.org/docs/9.1/static/sql-syntax-calling-funcs.html and here - postgresql.org/docs/9.1/static/plpgsql-statements.html. Honestly, you may want to at least skim through most of chapter 39, if you haven't already.

Collectives™ on Stack Overflow

pgsql sql functions sequential execution

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related