如何在 PostgreSQL 中为 CTE(公用表表达式)指定列类型? [英] How to specify column types for CTE (Common Table Expressions) in PostgreSQL?

问题描述

考虑

WITH t (f0, f1) as (
  values 
     (1, 10),
     (2, 20)     
)...

如何指定 f0 和 f1 是 bigint 类型?

How do I specify that f0 and f1 are of type bigint?

推荐答案

我认为你必须在你的情况下在 VALUES 表达式中指定类型:

I think you'd have to specify the types inside the VALUES expression in your case:

WITH t (f0, f1) as (
  values 
     (1::bigint, 10::bigint),
     (2, 20)
)...

你只需要第一组值的类型，PostgreSQL 可以推断出其余的.

You only need the types on the first set of values, PostgreSQL can infer the rest.

例如，假设我们有两个函数:

For example, suppose we have two functions:

create function f(bigint, bigint) returns bigint as $$
begin
    raise notice 'bigint';
    return $1 * $2;
end;
$$ language plpgsql;

create function f(int, int) returns int as $$
begin
    raise notice 'int';
    return $1 * $2;
end;
$$ language plpgsql;

然后

WITH t (f0, f1) as (
    values
        (1, 10),
        (2, 20)
)
select f(f0, f1) from t;

会给你两个 int 通知而

WITH t (f0, f1) as (
    values
        (1::bigint, 10::bigint),
        (2, 20)
)
select f(f0, f1) from t;

会给你两个 bigint 通知.

这篇关于如何在 PostgreSQL 中为 CTE(公用表表达式)指定列类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！