Doubt in function in PostgreSQL

Question

Doubt in function in PostgreSQL

Navigation

#1 by (3 votes)
#2 by (1 votes)

2

I'm doing a function in PostgreSQL and I want to use it to check whether a table already exists or not in a database, and if it does not exist, I want to create the table.

The problem is in the function statement, which receives varchar , which parameter will be used for SELECT relname and also create of the table.

Follow the function:

CREATE OR REPLACE FUNCTION verificarDb (tb varchar)RETURNS BOOLEAN as
$$
BEGIN
select relname from pg_class where relname = tb and relkind='r';
if not found then
        CREATE TABLE tb
        (
          id integer,
          nome varchar
        );
    return false;
end if;
if found then
return true;
end if; 
END;
$$
LANGUAGE plpgsql;
select verificarDb('tabela');

Verification is not being done by the contents of the tb parameter, but rather by using the string tb . In create is also using the acronym tb , I want to use the table name passed by parameter, how do I?

sql postgresql

asked by anonymous 19.09.2018 / 02:03

2 answers

1

Try it this way:

CREATE OR REPLACE FUNCTION verificarDb(tb regclass, OUT result boolean) AS
$$
BEGIN
    EXECUTE format('SELECT EXISTS   (
                                        SELECT 1 
                                        FROM   pg_class c
                                        WHERE  relname = ''%s''
                                        AND    c.relkind = ''r''
                                    );', tb)
    INTO result;

    IF result = FALSE THEN
        CREATE TABLE tb
        (
          id    INTEGER,
          nome  VARCHAR
        );
    END IF;
END;
$$  
LANGUAGE plpgsql;

How to use it:

SELECT verificarDb('tabela');

Since I come from SQL Server , the syntax and structure may have to be adjusted, but I think it will not be difficult.

19.09.2018 / 13:51

Generate new image centered on a point Code before or after super

score 3 · Accepted Answer

I was able to complete the function, here's the code sketch below:

    CREATE OR REPLACE FUNCTION verificarDb (tb varchar)RETURNS BOOLEAN as
    $$
    DECLARE result boolean;
    BEGIN
     execute format ('SELECT EXISTS (select 1 from pg_class where relname = 
    ''%s'' and relkind=''r'');', tb)
    INTO result;
    if result = FALSE then
      EXECUTE format ('CREATE TABLE "%s"
      (
        id integer,
        nome varchar
      );', tb);

        RETURN FALSE;
      ELSE
        RETURN TRUE;
      end if; 
     END;
     $$
     LANGUAGE plpgsql;
     --teste de função -> select verificarDb('tabela');