为什么RETURN QUERY返回字符串而不是TABLE

Question

这个MWE不是通常如何解决这个问题，但是，它很简单，我可以解释我遇到的问题。我只是想指出两件事

1. 我所做的不仅仅是返回表的内容
2. 返回的不是表，而是字符串

支持SQL语句：

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
c test
CREATE TABLE credit_card(
id       BIGSERIAL PRIMARY KEY,
balance  BIGINT
);

功能：

CREATE FUNCTION get_credit_card(
p_id     BIGINT
)
RETURNS TABLE(
id       BIGINT,
balance  BIGINT
)
AS $$
DECLARE
BEGIN
RETURN QUERY
SELECT
credit_card.id,
credit_card.balance
FROM
credit_card
WHERE
credit_card.id = p_id;
END $$ LAnguage 'plpgsql';
CREATE FUNCTION pay_with_card(
p_id     BIGINT,
p_amount BIGINT
)
RETURNS TABLE(
id       BIGINT,
balance  BIGINT
)
AS $$
DECLARE
v_balance BIGINT;
BEGIN
SELECT
credit_card.balance
FROM
credit_card
INTO
v_balance
WHERE
credit_card.id = p_id;
IF v_balance < p_amount
THEN
RETURN;
END IF;
UPDATE
credit_card
SET
balance = credit_card.balance - p_amount;
RETURN QUERY
SELECT get_credit_card (p_id);
END $$ LAnguage 'plpgsql';

填充表和调用函数：

INSERT INTO credit_card
(balance)
VALUES
(100);
SELECT
pay_with_card (1, 100);

错误：

DROP DATABASE
CREATE DATABASE
You are now connected to database "test" as user "postgres".
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
INSERT 0 1
psql:test.sql:74: ERROR:  structure of query does not match function result type
DETAIL:  Returned type record does not match expected type bigint in column 1.
CONTEXT:  PL/pgSQL function pay_with_card(bigint,bigint) line 24 at RETURN QUERY

我花了很长时间才弄清楚pay_with_card返回的是String，或者看起来是String的东西，而不是TABLE(id BIGINT, balance BIGINT)。对于Pythonpsycopg2库，返回的查询是

[('(1,100)'),]

所以我的整个代码都崩溃了，因为我无法获得值(除非我破解它并使用字符串操作

问题：

我如何修复它，使它像一样返回正确的查询

[(1,100),]

Answer 1

可以替换horse_with_no_name注释中的提示

RETURN QUERY
SELECT get_credit_card (p_id);

带有

RETURN QUERY SELECT (get_credit_card(p_id)).*;

您需要以某种方式将返回的记录向后扩展到其组成字段中。(我认为马的SELECT * …也有同样的效果。(