如何在postgresql中使用for循环创建多个表 [英] How to create multiple tables using for loop in postgresql

问题描述

如何在 postgresql 中使用 for 循环创建多个表?

How does one create multiple tables using a for loop in postgresql?

例如:我需要创建 c_emloyee、r_employee、i_employee 等.我在 FOR 附近遇到语法错误

For example : I need to create c_emloyee, r_employee, i_employee, etc. I got a syntax error near FOR

! /bin/sh

#Invoke postgre
 SQLVARCHAR="varchar"
 SQLINTEGER="integer"
 SQLBIGINT="bigint"
 SQLSMALLINT="smallint"
 SQLTINYINT="smallint"
 SQLCIDR="cidr"
 SQLBINARY="varbinary"
 SQLTIME="timestamp"
 SQLMACADDRESS="macaddr"
 prefix[0]=c_
 prefix[1]=r_
 prefix[2]=s_
 prefix[3]=i_
 echo ${prefix[0]}
 echo ${prefix[1]}
 echo ${prefix[2]}
 echo ${prefix[3]}

 psql -d postgres <<EOF
 BEGIN 
 FOR i IN 0 1 2 3 LOOP 
 create table ${prefix[i]}employee (e_name $SQLVARCHAR(32) primary key, type $SQLTINYINT not null, description $SQLVARCHAR(128), ip_address $SQLCIDR);
 END LOOP;
 END;

推荐答案

你犯了两个错误:

• FOR、BEGIN ... END 等是 PL/PgSQL 的一部分，而不是常规 SQL.你不能在普通的 SQL 中使用这些，你需要一个 DO 块或 CREATE OR REPLACE FUNCTION.

• FOR, BEGIN ... END, etc are part of PL/PgSQL, not regular SQL. You can't use these in plain SQL, you need a DO block or CREATE OR REPLACE FUNCTION.

您对评估顺序感到非常困惑.您已经在 i 上编写了一个 PL/PgSQL FOR 循环，然后在 bash 变量扩展中引用了 i.这将在 PL/PgSQL 函数文本的生成过程中进行扩展，在变量 i 存在之前.

You're seriously muddled about the order of evaluation. You've written a PL/PgSQL FOR loop over i, then you reference i in a bash variable expansion. That'll be expanded during the generation of the PL/PgSQL function text, way before the variable i ever exists.

如果您将 psql -d postgres 替换为 cat，您可以清楚地看到后一个问题，以便它打印您尝试运行的生成的 SQL:

You can see the latter problem clearly if you just replace psql -d postgres with cat so it prints the generated SQL you're trying to run:

 BEGIN
 FOR i IN 0 1 2 3 LOOP
 create table c_employee (e_name varchar(32) primary key, type smallint not null, description varchar(128), ip_address cidr);
 END LOOP;
 END;

如您所见，${prefix[i]} 的计算结果为 c_ 因为 i 未定义，因此被视为零猛击.因此，您的 prefix 数组中的其他条目将永远不会被使用.

As you can see, ${prefix[i]} evaluated to c_ because i, being undefined, was treated as zero by bash. So the other entries in your prefix array will never be used.

您需要:

• 使用 DO 块或 CREATE OR REPLACE FUNCTION 和函数调用来执行您的 PL/PgSQL 代码；和

• Use DO block or CREATE OR REPLACE FUNCTION and a function call to execute your PL/PgSQL code; and

使用EXECUTE format(...)运行动态SQL

或者，您可以在 bash for 循环中生成纯 SQL CREATE TABLE 语句，完全不需要 PL/PgSQL.我会使用这种方法，因为它更简单.

Alternately, you can generate the plain SQL CREATE TABLE statements in a bash for loop, doing away entirely with the need for PL/PgSQL. I would use this approach, as it's much simpler.

for p in ${prefix[*]}; do
  echo "create table ${p}employee (e_name $SQLVARCHAR(32) primary key, type $SQLTINYINT not null, description $SQLVARCHAR(128), ip_address $SQLCIDR);"
done | psql

顺便说一下，没有postgre这样的东西.我想你的意思是Postgres"或PostgreSQL".

By the way, there is no such thing as postgre. I think you meant "Postgres" or "PostgreSQL".

您可能想要 bytea 而不是 varbinary.无论如何，SQL 类型作为变量有什么用?您是否正在尝试编写 DDL 生成系统?如果是这样，请不要重新发明那个轮子，已经有很多这样的轮子了.

You probably want bytea not varbinary. What's with the SQL types as variables, anyway? Are you trying to write a DDL generation system? If so, don't reinvent that wheel, there are lots of them already out there.

此外，如果您正在做这样的事情，您很有可能需要阅读架构(如果您正在尝试进行多租户)、表分区等.

Also, if you're doing things like this, there's a fairly strong chance you need to read up on schemas (if you're trying to do multi-tenant), table partitioning, etc.

这篇关于如何在postgresql中使用for循环创建多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！