将存储过程从SQL Server迁移到PostgreSQL时出现的问题 [英] Issue when migrating Stored Procedure from SQL Server to PostgreSQL

问题描述

我从SQL Server到PostgreSQL（9.0），所以我在执行存储过程/函数时遇到一个问题。该函数返回此错误消息：

SQLSTATE：42601;

SQLERRM：查询没有结果数据的目标

我需要做的是从 SELECT 查询以及 OUT 参数到结果中，并避免得到该错误消息？

  CREATE OR REPLACE FUNCTION myplfunction（
在i_param1字符变化，
在i_param2字符变化，
在i_param3字符变化，
在i_param4字符变化，
 OUT o_call_status integer，
 OUT o_call_message character varying）
 RETURNS记录AS 
 $ BODY $ 
 DECLARE 
 val1 varchar; 
 val2 varchar; 
 ... 
 
 IF（v_solution_id IS NULL）THEN 
 val1：= column1 FROM tbl2 WHERE column2 = i_param1; 
 IF（val1 IS NULL）THEN 
 o_call_status：= 1005; 
 o_call_message：= column1未配置或无效'; 
 RETURN; 
 END IF; 
 
 SELECT'mycolumnname1'作为paramName，mycolumn1作为值FROM tb1 WHERE column1 = val 
 UNION ALL 
 SELECT'mycolumnname2'作为paramName ,, mycolumn2作为值FROM tb1 WHERE column1 = val 
 UNION ALL 
 SELECT'mycolumnname2'as paramName ,, mycolumn2 as value FROM tb2 WHERE column2 = val2 
 WHERE tb2paramName4 = i_val3; 
 
 o_call_status：= 0; 
 o_call_message：=''; 
 
 EXCEPTION WHEN OTHERS THEN 
 o_call_message：= SQLERRM; 
 o_call_status：= SQLSTATE; 
 END; 
 $ BODY $ 
 LANGUAGE plpgsql VOLATILE 
  

解决方案

通常，PostgreSQL函数只返回单个值（或行）或一组值（或行），但不能同时并行。

返回一个表

您可以直接从函数返回一个或多个行，使其成为设置返回函数， strong>表函数。然后你没有别的选择，除了返回参数 o_call_status 和 o_call_message 与每一行（如果你，

  CREATE OR REPLACE FUNCTION myplfunction（i_val1 int，i_val2 int）
 RETURNS TABLE col1文本，col2文本 - 使用匹配类型
，o_call_status text  - 文本，不是int 
，o_call_message文本）AS 
 $ func $ 
 BEGIN 
 RETURN QUERY 
 SELECT col1，col2 
，'0':: text AS o_call_status 
，'':: text AS o_call_message  - 列别名不相关
 FROM tb1 
 WHERE col3 = i_val1 
 UNION ALL 
 SELECT col4，col5，'0':: text，'':: text 
 FROM tb2 
 WHERE col6 = i_val2 
 AND col7 = i_val3; -  WHERE被指定两次
 
 EXCEPTION WHEN OTHERS THEN 
 RETURN QUERY 
 SELECT NULL :: text，NULL :: text，SQLERRM，SQLSTATE; 
 END 
 $ func $ LANGUAGE plpgsql; 
  

特殊变量 SQLERRM code> text ，btw。 $ <$>

或打开光标

或者你必须打开一个游标，将返回的表从这两个参数中解耦。

  CREATE OR REPLACE FUNCTION myplfunction $ b i_val1 text，i_val2 text，i_val3 text，i_val4 text 
，OUT o_call_status text 
，OUT o_call_message text）AS 
 $ func $ 
 DECLARE 
 curs CURSOR FOR 
 SELECT col1，col2 FROM tb1 
 WHERE col3 = i_val1 
 UNION ALL 
 SELECT col4，col5 FROM tb2 
 WHERE col6 = i_val2 
 AND col7 = i_val3 ; 
 BEGIN 
 OPEN curs; 
 
 o_call_status：='0'; 
 o_call_message：=''; 
 
 EXCEPTION WHEN OTHERS THEN 
 o_call_status：= SQLSTATE; 
 o_call_message：= SQLERRM; 
 END 
 $ func $ LANGUAGE plpgsql; 
  

但，游标只存在于同一个交易中。因此，您需要在 COMMIT 或 ROLLBACK 之前获取值。

$ b b

  BEGIN; 
 SELECT * FROM myplfunction（10，20，30，40）; 
 FETCH ALL IN curs; 
 ROLLBACK; 
  

你甚至可以递交任意游标名称。 手册中的此信息和其他详情。

还有有关Postgres 9.3之后可用错误的详细信息。。

I come from SQL Server to PostgreSQL (9.0), so I am having a issue in a stored procedure / function when executing it. The function is returning this error message:

SQLSTATE: 42601;
SQLERRM: query has no destination for result data

What I need to do pass the values from columns selected by the SELECT query along with the OUT parameters into the result and avoid getting that error message?

CREATE OR REPLACE FUNCTION myplfunction(
    IN i_param1 character varying,
    IN i_param2 character varying,
    IN i_param3 character varying,
    IN i_param4 character varying,
    OUT o_call_status integer,
    OUT o_call_message character varying)
  RETURNS record AS
$BODY$
DECLARE 
val1 varchar;
val2 varchar;
...

IF (v_solution_id IS NULL) THEN
    val1 := column1 FROM tbl2 WHERE column2= i_param1;
    IF(val1 IS NULL) THEN
        o_call_status := 1005;
        o_call_message := column1 is not configured or invalid';
        RETURN;
END IF;

    SELECT 'mycolumnname1' as paramName,mycolumn1 as value FROM tb1 WHERE column1 = val
    UNION ALL
    SELECT 'mycolumnname2' as paramName,,mycolumn2 as value  FROM tb1 WHERE column1 = val
    UNION ALL
    SELECT 'mycolumnname2' as paramName,,mycolumn2 as value  FROM tb2 WHERE column2 = val2 
    WHERE tb2paramName4=i_val3;      

    o_call_status := 0;
    o_call_message := '';

    EXCEPTION WHEN OTHERS THEN 
        o_call_message := SQLERRM;
        o_call_status := SQLSTATE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

解决方案

Generally, a PostgreSQL function only returns a single value (or row), or a set of values (or rows), but not both in parallel.

Return a table

You can either return the set or rows directly from the function, making it a "set-returning function", a.k.a. "table function". Then you have no other choice than to return the parameters o_call_status and o_call_message with every row (if you, in fact, need them at all?!)

CREATE OR REPLACE FUNCTION myplfunction(i_val1 int, i_val2 int)
  RETURNS TABLE (col1 text, col2 text          -- use matching types!
               , o_call_status  text           -- text, not int
               , o_call_message text) AS
$func$
BEGIN
   RETURN QUERY 
   SELECT col1, col2
        , '0'::text AS o_call_status
        , ''::text AS o_call_message   -- column aliases irrelevant here
   FROM   tb1
   WHERE  col3 = i_val1
   UNION ALL
   SELECT col4, col5, '0'::text, ''::text
   FROM   tb2
   WHERE  col6 = i_val2
   AND    col7 = i_val3;      -- WHERE was specified twice

   EXCEPTION WHEN OTHERS THEN 
     RETURN QUERY
     SELECT NULL::text, NULL::text, SQLERRM, SQLSTATE;
END
$func$  LANGUAGE plpgsql;

The special variable SQLERRM is of type text, btw. Not integer.

Or open a cursor

Or you would have to open a cursor to decouple the returned table from those two parameters.

CREATE OR REPLACE FUNCTION myplfunction(
      i_val1 text, i_val2 text, i_val3 text, i_val4 text
    , OUT o_call_status  text
    , OUT o_call_message text) AS
$func$
DECLARE
   curs CURSOR FOR
   SELECT col1, col2 FROM tb1
   WHERE  col3 = i_val1
   UNION ALL
   SELECT col4, col5 FROM tb2
   WHERE  col6 = i_val2
   AND    col7 = i_val3;
BEGIN
   OPEN curs;

   o_call_status  := '0';
   o_call_message := '';

   EXCEPTION WHEN OTHERS THEN 
     o_call_status  := SQLSTATE;
     o_call_message := SQLERRM;
END
$func$  LANGUAGE plpgsql;

However, the cursor only exists within the same transaction. So you need to fetch the values before you COMMIT or ROLLBACK.

BEGIN;
SELECT * FROM myplfunction (10, 20, 30, 40);
FETCH ALL IN curs;
ROLLBACK;

You can even hand in arbitrary cursor names. This and other details in the manual.

And there is more information about errors available since Postgres 9.3..

这篇关于将存储过程从SQL Server迁移到PostgreSQL时出现的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！