Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd3c3f2b

Browse files
committed
Teach PL/pgSQL about partitioned tables.
Table partitioning, introduced in commitf0e4475, added a newrelkind - RELKIND_PARTITIONED_TABLE. Update a couple of places inPL/pgSQL to handle it. Specifically plpgsql_parse_cwordtype() andbuild_row_from_class() needed updating in order to make table%ROWTYPEand table.col%TYPE work for partitioned tables.Dean Rasheed, reviewed by Amit Langote.Discussion:https://postgr.es/m/CAEZATCUnNOKN8sLML9jUzxecALWpEXK3a3W7y0PgFR4%2Buhgc%3Dg%40mail.gmail.com
1 parentf356ec5 commitd3c3f2b

File tree

3 files changed

+88
-2
lines changed

3 files changed

+88
-2
lines changed

‎src/pl/plpgsql/src/pl_comp.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1761,7 +1761,8 @@ plpgsql_parse_cwordtype(List *idents)
17611761
classStruct->relkind!=RELKIND_VIEW&&
17621762
classStruct->relkind!=RELKIND_MATVIEW&&
17631763
classStruct->relkind!=RELKIND_COMPOSITE_TYPE&&
1764-
classStruct->relkind!=RELKIND_FOREIGN_TABLE)
1764+
classStruct->relkind!=RELKIND_FOREIGN_TABLE&&
1765+
classStruct->relkind!=RELKIND_PARTITIONED_TABLE)
17651766
gotodone;
17661767

17671768
/*
@@ -1987,7 +1988,8 @@ build_row_from_class(Oid classOid)
19871988
classStruct->relkind!=RELKIND_VIEW&&
19881989
classStruct->relkind!=RELKIND_MATVIEW&&
19891990
classStruct->relkind!=RELKIND_COMPOSITE_TYPE&&
1990-
classStruct->relkind!=RELKIND_FOREIGN_TABLE)
1991+
classStruct->relkind!=RELKIND_FOREIGN_TABLE&&
1992+
classStruct->relkind!=RELKIND_PARTITIONED_TABLE)
19911993
ereport(ERROR,
19921994
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
19931995
errmsg("relation \"%s\" is not a table",relname)));

‎src/test/regress/expected/plpgsql.out

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5979,3 +5979,48 @@ LINE 1: SELECT (SELECT string_agg(id || '=' || name, ',') FROM d)
59795979
^
59805980
QUERY: SELECT (SELECT string_agg(id || '=' || name, ',') FROM d)
59815981
CONTEXT: PL/pgSQL function alter_table_under_transition_tables_upd_func() line 3 at RAISE
5982+
--
5983+
-- Check type parsing and record fetching from partitioned tables
5984+
--
5985+
CREATE TABLE partitioned_table (a int, b text) PARTITION BY LIST (a);
5986+
CREATE TABLE pt_part1 PARTITION OF partitioned_table FOR VALUES IN (1);
5987+
CREATE TABLE pt_part2 PARTITION OF partitioned_table FOR VALUES IN (2);
5988+
INSERT INTO partitioned_table VALUES (1, 'Row 1');
5989+
INSERT INTO partitioned_table VALUES (2, 'Row 2');
5990+
CREATE OR REPLACE FUNCTION get_from_partitioned_table(partitioned_table.a%type)
5991+
RETURNS partitioned_table AS $$
5992+
DECLARE
5993+
a_val partitioned_table.a%TYPE;
5994+
result partitioned_table%ROWTYPE;
5995+
BEGIN
5996+
a_val := $1;
5997+
SELECT * INTO result FROM partitioned_table WHERE a = a_val;
5998+
RETURN result;
5999+
END; $$ LANGUAGE plpgsql;
6000+
NOTICE: type reference partitioned_table.a%TYPE converted to integer
6001+
SELECT * FROM get_from_partitioned_table(1) AS t;
6002+
a | b
6003+
---+-------
6004+
1 | Row 1
6005+
(1 row)
6006+
6007+
CREATE OR REPLACE FUNCTION list_partitioned_table()
6008+
RETURNS SETOF partitioned_table.a%TYPE AS $$
6009+
DECLARE
6010+
row partitioned_table%ROWTYPE;
6011+
a_val partitioned_table.a%TYPE;
6012+
BEGIN
6013+
FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP
6014+
a_val := row.a;
6015+
RETURN NEXT a_val;
6016+
END LOOP;
6017+
RETURN;
6018+
END; $$ LANGUAGE plpgsql;
6019+
NOTICE: type reference partitioned_table.a%TYPE converted to integer
6020+
SELECT * FROM list_partitioned_table() AS t;
6021+
t
6022+
---
6023+
1
6024+
2
6025+
(2 rows)
6026+

‎src/test/regress/sql/plpgsql.sql

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4766,3 +4766,42 @@ ALTER TABLE alter_table_under_transition_tables
47664766
DROP column name;
47674767
UPDATE alter_table_under_transition_tables
47684768
SET id= id;
4769+
4770+
--
4771+
-- Check type parsing and record fetching from partitioned tables
4772+
--
4773+
4774+
CREATETABLEpartitioned_table (aint, btext) PARTITION BY LIST (a);
4775+
CREATETABLEpt_part1 PARTITION OF partitioned_table FORVALUESIN (1);
4776+
CREATETABLEpt_part2 PARTITION OF partitioned_table FORVALUESIN (2);
4777+
4778+
INSERT INTO partitioned_tableVALUES (1,'Row 1');
4779+
INSERT INTO partitioned_tableVALUES (2,'Row 2');
4780+
4781+
CREATE OR REPLACEFUNCTIONget_from_partitioned_table(partitioned_table.a%type)
4782+
RETURNS partitioned_tableAS $$
4783+
DECLARE
4784+
a_valpartitioned_table.a%TYPE;
4785+
result partitioned_table%ROWTYPE;
4786+
BEGIN
4787+
a_val := $1;
4788+
SELECT* INTO resultFROM partitioned_tableWHERE a= a_val;
4789+
RETURN result;
4790+
END; $$ LANGUAGE plpgsql;
4791+
4792+
SELECT*FROM get_from_partitioned_table(1)AS t;
4793+
4794+
CREATE OR REPLACEFUNCTIONlist_partitioned_table()
4795+
RETURNS SETOFpartitioned_table.a%TYPEAS $$
4796+
DECLARE
4797+
row partitioned_table%ROWTYPE;
4798+
a_valpartitioned_table.a%TYPE;
4799+
BEGIN
4800+
FOR rowINSELECT*FROM partitioned_tableORDER BY a LOOP
4801+
a_val :=row.a;
4802+
RETURN NEXT a_val;
4803+
END LOOP;
4804+
RETURN;
4805+
END; $$ LANGUAGE plpgsql;
4806+
4807+
SELECT*FROM list_partitioned_table()AS t;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp