Create procedure to execute query in PostgreSQL

Question

Maybe it's not new case, but I'm stack about it. This is the procedure that I use to run query, it run normally in MySQL, but not in PostgreSQL and I don't know how to do that. The procedure(in MySQL) looks like :

CREATE PROCEDURE runstatement(IN statement TEXT)
BEGIN 
  SET @s = statement; 
  IF LENGTH(@s) <> 0 THEN PREPARE stmt FROM @s; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt; 
  END IF; 
END

The questions:

1. How do I convert it to PostgreSQL version?
2. How do I call this procedure(runstatement) when I need it in another procedure? In MySQL I know as CALL runstatement(param).

Thanks for each reply. I'm new in database programming especially PostgreSQL.

Answer 1

You can create a procedure in PL/pgSQL or SQL language.

For example, you create test table as shown below:

CREATE TABLE test (
  num INTEGER
);

Then, you insert the row whose num is 2 as shown below:

INSERT INTO test (num) VALUES (2);

Now, you can create my_proc() PL/pgSQL procedure which adds value to num and returns value to the caller as shown below:

CREATE PROCEDURE my_proc(IN value INTEGER, OUT result INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + value;
  SELECT num INTO result FROM test;
END;
$$;

Or, you can create my_proc() SQL procedure which adds value to num and returns value to the caller as shown below:

CREATE PROCEDURE my_proc(IN value INTEGER, OUT result INTEGER)
LANGUAGE SQL
AS $$
UPDATE test SET num = num + value;
SELECT num FROM test;
$$;

Then, you can call my_proc(3, 10) with CALL statement, then 5 is returned and 3 is added to num as shown below:

postgres=# CALL my_proc(3, 10);
 result
--------
      5
(1 row)

postgres=# SELECT num FROM test;
 num
-----
   5
(1 row)

Answer 2

how to create stored procedures in postgresql 11 and dbconnection with .net.
if we have older version we don't have procedures. now we have procedures in postgresql version 11. This is new postgresql version 11.

        -- DROP PROCEDURE public.sp_lite_web_login_conn(text, character varying, xml, xml);
        -- call sp_lite_web_login_conn('PG_LOAD', 'LG570', 'one', 'two')
        CREATE OR REPLACE PROCEDURE public.sp_lite_web_login_conn(
                        p_flag text,
                        p_site character varying,
                        INOUT result_one refcursor,
                        INOUT result_two refcursor)
        LANGUAGE 'plpgsql'
        AS $BODY$         
        BEGIN
              open result_one FOR SELECT 'NOT_OK' AS STATUS;
              open result_two FOR SELECT 'NOT_OK' AS STATUS;                                       
        END;
        $BODY$;

the below procedures refcursor based select query.

           public DataSet executeSelectQuery_POST_PROC(string _query, NpgsqlParameter[] sqlParameter)
                {
                    NpgsqlConnection npg_conn1 = new NpgsqlConnection(connstring);
                    try
                    {
                        npg_conn1.Open();
                        NpgsqlTransaction tran = npg_conn1.BeginTransaction();
                        DataSet ds = new DataSet();
                        DataTable dt = new DataTable();
                        NpgsqlCommand command = new NpgsqlCommand(_query, npg_conn1);
                        command.CommandType = CommandType.Text;
                        command.Parameters.AddRange(sqlParameter);
                        command.ExecuteNonQuery();
                        NpgsqlDataAdapter da;
                        int i = 0;
                        foreach (NpgsqlParameter parm in sqlParameter)
                        {
                            if (parm.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Refcursor)
                            {
                                string parm_val = string.Format("FETCH ALL IN \"{0}\"", parm.Value.ToString());
                                da = new NpgsqlDataAdapter(parm_val.Trim().ToString(), npg_conn1);
                                ds.Tables.Add(parm_val);
                                da.Fill(ds.Tables[i]);
                                i++;
                            }
                        }
                        tran.Commit();
                        return ds;
                    }
                    catch (Exception ex)
                    {
                        return null;
                    }
                    finally
                    {
                        npg_conn1.Close();
                    }
                }

user control

  public DataSet db_validation(string flag, string site)
        {
            string query = string.Format(@"call sp_lite_web_login_conn('" + flag + "','" + site + "',@first_tbl,@second_tbl)");
            NpgsqlParameter[] sqlParameters = new NpgsqlParameter[4];
            sqlParameters[0] = new NpgsqlParameter("@p_flag", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(flag);
            sqlParameters[1] = new NpgsqlParameter("@p_site", SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString(site);
            //
            sqlParameters[2] = new NpgsqlParameter("@first_tbl", NpgsqlTypes.NpgsqlDbType.Refcursor);
            sqlParameters[2].Value = Convert.ToString("first_tbl");
            sqlParameters[2].Direction = ParameterDirection.InputOutput;
            sqlParameters[2].NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor;
            sqlParameters[3] = new NpgsqlParameter("@second_tbl", NpgsqlTypes.NpgsqlDbType.Refcursor);
            sqlParameters[3].Value = Convert.ToString("second_tbl");
            sqlParameters[3].Direction = ParameterDirection.InputOutput;
            sqlParameters[3].NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor;

            return conn.executeSelectQuery_POST_PROC(query, sqlParameters);
        }

Answer 3

This technique is named dynamic SQL. PLpgSQL has EXECUTE statement for this case. Probably is useless to write special function just for this, because EXECUTE is one line command.

CREATE OR REPLACE FUNCTION runstatement(statement TEXT)
RETURNS void AS $$
BEGIN
  IF statement <> '' THEN
    EXECUTE statement;
  END IF;
END;
$$ LANGUAGE plpgsql;

Probably test on empty string is bad design. This case should not to be. Assert is better there.

This function can be called:

SELECT runstatement(''); -- outside plpgsql

or

PERFORM runstatement('') -- inside plpgsql

See related part in documentation.