前言
此博客翻译自 percona 官网博客 《PostgreSQL PL/Java – A How-To, Part 2: How to Manipulate and Return Tuples》
我们在本系列的第一部分中讨论了如何安装和创建一个简单的类，我们在其中运行了一个 SELECT 并返回一行和一列，其中包含格式化的文本。现在是时候展开并看看如何返回多个元组了。
这里有一点免责声明；我不会对 Java 代码发表太多评论，因为这不是一个 Java 教程。这里的示例仅用于教育目的，并非旨在提供高性能或用于生产！
返回表结构
第一个示例将展示我们如何从 PL/Java 函数中选择和返回表。我们将在这里继续使用“客户”表，探测 SQL 如下：
SELECT * FROM customer LIMIT 10;
我将在这里创建一个名为 CustomerResultSet 的新 Java 类，初始代码为：
package com.percona.blog.pljava;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.postgresql.pljava.ResultSetHandle;
public class CustomerResultSet implements ResultSetHandle {
private Connection conn;
private PreparedStatement stmt;
private final String m_url = "jdbc:default:connection";
private final String sql = "select * FROM customer LIMIT 10";
public CustomerResultSet() throws SQLException {
conn = DriverManager.getConnection(m_url);
stmt = conn.prepareStatement(sql);
}
@Override
public void close() throws SQLException {
stmt.close();
conn.close();
}
@Override
public ResultSet getResultSet() throws SQLException {
return stmt.executeQuery();
}
public static ResultSetHandle getCustomerPayments() throws SQLException {
return new CustomerResultSet();
}
}
注意，我们实现 PL/Java 提供的 org.postgresql.pljava.ResultSetHandle 接口。因为我们正在返回一个复杂的对象，并且当我们不需要操作返回的元组时，使用 ResultSetHandle 接口。
现在我们正在使用 PL/Java 对象，我们需要告诉编译器在哪里可以找到这些引用，对于这里的第一个示例，我们需要 pljava-api jar，在我的例子中恰好是 pljava-api-1.6.2.jar，如果你还记得我在第一篇文章里编译的代码，我在这里使用的 PL/Java 和我的 JAR 文件位于~/pljava-1_6_2/pljava-api/target/pljava-api-1.6.2.jar，编译命令如下：
javac -cp "~/pljava-1_6_2/pljava-api/target/pljava-api-1.6.2.jar" com/percona/blog/pljava/CustomerResultSet.java
jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/CustomerResultSet.class
创建新的 JAR 文件后，我可以将其安装到 Postgres 并创建函数 getCustomerLimit10()：
SELECT sqlj.install_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );
SELECT sqlj.set_classpath( 'public', 'pljavaPart2' );
CREATE OR REPLACE FUNCTION getCustomerLimit10() RETURNS SETOF customer AS 'com.percona.blog.pljava.CustomerResultSet.getCustomerLimit10' LANGUAGE java;
函数调用的结果是：
test=# SELECT * FROM getCustomerLimit10();
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | active
-------------+----------+------------+-----------+-------------------------------------+------------+------------+-------------+---------------------+--------
1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
4 | 2 | BARBARA | JONES | BARBARA.JONES@sakilacustomer.org | 8 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
5 | 1 | ELIZABETH | BROWN | ELIZABETH.BROWN@sakilacustomer.org | 9 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
6 | 2 | JENNIFER | DAVIS | JENNIFER.DAVIS@sakilacustomer.org | 10 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
7 | 1 | MARIA | MILLER | MARIA.MILLER@sakilacustomer.org | 11 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
8 | 2 | SUSAN | WILSON | SUSAN.WILSON@sakilacustomer.org | 12 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
9 | 2 | MARGARET | MOORE | MARGARET.MOORE@sakilacustomer.org | 13 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
10 | 1 | DOROTHY | TAYLOR | DOROTHY.TAYLOR@sakilacustomer.org | 14 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
(10 rows)
test=#
在返回之前操作结果
返回普通 SQL 的结果有其用途，例如可见性/权限控制，但是我们通常需要在返回之前操作查询的结果，为此我们可以实现接口org.postgresql.pljava.ResultSetProvider。
在下面的示例中，我将实现一个简单的方法来使用哈希函数对敏感数据进行匿名化。我还将创建一个帮助类来处理哈希函数和加密函数，以保持 CustomerResultSet 类的干净：
/**
* Crypto helper class that will contain all hashing and cryptographic functions
*/
package com.percona.blog.pljava;
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
public class Crypto {
MessageDigest digest;
public Crypto() throws NoSuchAlgorithmException {
digest = MessageDigest.getInstance("SHA-256");
}
public String bytesToHex(byte[] hash) {
StringBuilder hexString = new StringBuilder(2 * hash.length);
for (int i = 0; i < hash.length; i++) {
String hex = Integer.toHexString(0xff & hash[i]);
if (hex.length() == 1) {
hexString.append('0');
}
hexString.append(hex);
}
return hexString.toString();
}
public String encode(String data, int min, int max) {
double salt = Math.random();
int sbstring = (int) ((Math.random() * ((max - min) + 1)) + min);
return bytesToHex(digest.digest((data + salt).getBytes(StandardCharsets.UTF_8))).substring(0, sbstring);
}
}
/**
* CustomerHash class
*/
package com.percona.blog.pljava;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.postgresql.pljava.ResultSetProvider;
public class CustomerHash implements ResultSetProvider {
private final Connection conn;
private final PreparedStatement stmt;
private final ResultSet rs;
private final Crypto crypto;
private final String m_url = "jdbc:default:connection";
public CustomerHash(int id) throws SQLException, NoSuchAlgorithmException {
String query;
crypto = new Crypto();
query = "SELECT * FROM customer WHERE customer_id = ?";
conn = DriverManager.getConnection(m_url);
stmt = conn.prepareStatement(query);
stmt.setInt(1, id);
rs = stmt.executeQuery();
}
@Override
public void close() throws SQLException {
stmt.close();
conn.close();
}
@Override
public boolean assignRowValues(ResultSet receiver, int currentRow) throws SQLException {
if (!rs.next())
return false;
try {
receiver.updateInt(1, rs.getInt("customer_id"));
receiver.updateInt(2, rs.getInt("store_id"));
receiver.updateString(3, crypto.encode(rs.getString("first_name"), 5, 45));
receiver.updateString(4, crypto.encode(rs.getString("last_name"), 5, 45));
receiver.updateString(5, crypto.encode(rs.getString("email"), 5, 41) + "@mail.com");
receiver.updateInt(6, rs.getInt("address_id"));
receiver.updateBoolean(7, rs.getBoolean("activebool"));
receiver.updateDate(8, rs.getDate("create_date"));
receiver.updateTimestamp(9, rs.getTimestamp("last_update"));
receiver.updateInt(10, rs.getInt("active"));
} catch (Exception e) {
Logger.getAnonymousLogger().log(Level.parse("SEVERE"), e.getMessage());
}
return true;
}
public static ResultSetProvider getCustomerAnonymized(int id) throws SQLException, NoSuchAlgorithmException {
return new CustomerHash(id);
}
}
类的数量在增加，所以我们不再一一提及，而是使用“.java”来构建类，使用“.class”来创建 jar：
javac -cp "~/pljava-1_6_2/build/pljava-api-1.6.2.jar" com/percona/blog/pljava/*.java
jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/*.class
请记住，每次我们更改 JAR 文件时，我们都需要将其重新加载到 Postgres 中。检查下一个示例，您会看到我在重新加载 JAR 文件，创建并测试我们的新函数/方法：
test=# SELECT sqlj.replace_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );
replace_jar
-------------
(1 row)
test=# CREATE OR REPLACE FUNCTION getCustomerAnonymized(int) RETURNS SETOF customer AS 'com.percona.blog.pljava.CustomerHash.getCustomerAnonymized' LANGUAGE java;
CREATE FUNCTION
test=# SELECT * FROM getCustomerAnonymized(9);
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | ac
tive
-------------+----------+---------------------+-------------------------------------+-----------------------------------------+------------+------------+-------------+---------------------+---
-----
9 | 2 | 72e2616ef0075e81929 | 3559c00ee546ae0062460c8faa4f24960f1 | 24854ed40ed42b57f077cb1cfaf916@mail.com | 13 | t | 2006-02-14 | 2006-02-15 09:57:20 |
1
(1 row)
test=#
很棒！我们现在有一个匿名数据的方法了！
触发器
第二部分的最后一个主题是关于“触发器”，为了让它更有趣，我们将创建一个触发器来加密我们表的敏感数据。前面例子中匿名使用哈希函数很不错，但是如果我们未经授权访问数据库会发生什么？数据以纯文本形式保存！
为了使示例尽可能小，我不会费心保护密钥，因为我将在本系列的第三部分中使用 Java 访问外部资源，使用 Vault 来保护我们的密钥，敬请期待！
好的，我们需要做的第一件事是创建加密/解密数据所需的密钥对。我将使用“OpenSSL”来创建它们并将它们存储到一个名为“keys”的表中！
openssl genrsa -out keypair.pem 2048
openssl pkcs8 -topk8 -nocrypt -in keypair.pem -outform PEM -out private.pem
openssl rsa -in keypair.pem -outform PEM -pubout -out public.pem
现在我们有了密钥，我们需要清理密钥文件以从私钥和公钥中删除页眉和页脚数据，并删除所有断行，否则我们的 Java 代码会报错：
echo -n "CREATE TABLE keys(id int primary key, priv varchar not null, pub varchar not null); INSERT INTO keys VALUES(1, '" > keys.sql
cat private.pem | sed '1d;$d' | sed ':a;N;$!ba;s/\n//g' | tr -d '\n' >> keys.sql
echo -n "', '" >> keys.sql
cat public.pem | sed '1d;$d' | sed ':a;N;$!ba;s/\n//g' | tr -d '\n' >> keys.sql
echo -n "');" >> keys.sql
psql test < keys.sql
清理后看起来像这样：
CREATE TABLE keys(id int primary key, priv varchar not null, pub varchar not null); INSERT INTO keys VALUES(1, 'MIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQCiAA4BE64JZpXwIGfsUanyL//drIcFZ1cmiCW6zWOxc6nL8AQ33MPyQup8g/ociJFGn/eEEYOvRMV2pVNo3qB3VQU4WHRWkq22x7mRfuhHmAnAJA3dic5fiJ1aCQgo7tEqlGPc0WqL+jMUXh6Wmktq1kDZagUGJRorw0f5Iaj60PycbGtgKaKDc4VHepkN1jl0rhpJBzjBehuvB88LLXJ/cHsMOp3q569jLsHtqymCA2wP68ldtfKtOowPW9togIUmgWY0Z2lWlefrlzmT2g3L/oYbPUxCmptOAMFD8NajdA518ohZAC8SPfUsD4CwL89oPrMZlX4RkTuc5UvBHiKrAgMBAAECggEAcJl5ImZ7YS1cqjrcAPYCGcQjJAD3GFpryOx4zQ5VbNHoA0ggpnNb/tdkBIf3ID4MO/qUH8fMr9YtKfpfr1SOVGNT7YYN1t68v36zDN4YtSqIHHTy7jkKqHxcYmhEs67K072wa5tjY0fUmSOSPzufj/K7wGJge5TuS9y/+fnbafkdfW/yz3X2YXL6T/jfjqI4h+P7Nhh5hlpD1KZfEWTAY5B5tBoLc4xaTIB8FTLclVWw3CAW8h60EwUAkyxCSbrP2I1FCrWsV6hJGy8U+hUQJUpyDdum9ZC1oAVewRrCkSH0ZP1XaQifDZoRv/1N7cCbQqaLJaVk4rzVOEv0DoCEAQKBgQDOMPMm2ioEredCx0hfmHWWayGA5as7VPDSzv1QH3g4AdjZFf2YxctXGNJNMpfqVvFiQCWxp9NpjUPpODRbmR2J+7tYgx3B445zDeXdBH2JTKhUgNTHjL6DrM6FTI3yaSsSJ77L0mDcFQ42nfWtfqkZd5lYfwiVC0eL86bp408+YQKBgQDJIks6RqVlDbHerIIqT1ToN+CQ+BCjx/Z6sk4BFIKBB8sU8VyVkZlvQpFHvT06oE/1NbyiQ3nVufGrm0kwMqx7MXGiA670E1Q+Q/mQ12uRByUlgd+LW4mp1Y6tln1lpP5pVqUOC/jtnXYQmEReU4Ye24E4AZhFU23J+oYoh3XEiwKBgEJFaWFrbWXjnxjPhGt1TRXziOks6ERBoMWg0boW40TdEx1y+/dGW3y69ZzqTfl7yEmT5ImdL04VoWYsMmfeZqgayLRCMCZJRVeld+P5tX+Tq+a9Iaahjfo0aIxfdqAbPUSwkZphG9Cg09iqHHSO6TrOPfM7oT6GSZCp11QFQ0sBAoGAeABi+8D8mx8hmWY5Pv8X/HiiHjwyyVTbpPbO/Wv8NPmuW69per9k2PHRdgjdCCZvrjBCfFlfznljS+yZLQ1+xP2J+4zRDESgBYpO0vED94JY0lj7Q8z4hICq4Lyh0kwvki+kyI2yFirVLy950wFoSu7R2NVywSH2pgQ3mOTBCeMCgYBL5KIRf1qwsCYaCggPls4pWKMjfxxO915h26/aaniEYaTNnhXRSRwkVOWoGHoUKfrqQdrvj/y5lgezn7mZM0CvnB6ZkGwDXxpcIYUnhR1Lnp3HNSqfigg+WjQASVCKuq3YUri3p+KQkrpED/O3B4FJW2Q4IReEuREEsKNkeH96ew==', 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAogAOAROuCWaV8CBn7FGp8i//3ayHBWdXJoglus1jsXOpy/AEN9zD8kLqfIP6HIiRRp/3hBGDr0TFdqVTaN6gd1UFOFh0VpKttse5kX7oR5gJwCQN3YnOX4idWgkIKO7RKpRj3NFqi/ozFF4elppLatZA2WoFBiUaK8NH+SGo+tD8nGxrYCmig3OFR3qZDdY5dK4aSQc4wXobrwfPCy1yf3B7DDqd6uevYy7B7aspggNsD+vJXbXyrTqMD1vbaICFJoFmNGdpVpXn65c5k9oNy/6GGz1MQpqbTgDBQ/DWo3QOdfKIWQAvEj31LA+AsC/PaD6zGZV+EZE7nOVLwR4iqwIDAQAB');
往表插入完数据以后，我们有一个包含私钥和公钥的表。现在是创建我们的 Java 类的时候了。我将为加密函数重用“Crypto”类并创建一个新类来添加我们的触发函数。我将仅在此处添加 Crypto 类的相关部分，但您可以在我的 GitHub 主页上找到此处的完整代码，包括已经发布的第一部分（和即将发布的第三部分）。让我们进入代码：
/**
* This is the relevant part of the Crypto class that will encrypt and decrypt our data using the certificates we generated above.
*/
public PublicKey getPublicKey(String base64PublicKey) {
PublicKey publicKey = null;
try {
X509EncodedKeySpec keySpec = new X509EncodedKeySpec(Base64.getDecoder().decode(base64PublicKey.getBytes()));
KeyFactory keyFactory = KeyFactory.getInstance("RSA");
publicKey = keyFactory.generatePublic(keySpec);
return publicKey;
} catch (NoSuchAlgorithmException e) {
e.printStackTrace();
} catch (InvalidKeySpecException e) {
e.printStackTrace();
}
return publicKey;
}
public PrivateKey getPrivateKey(String base64PrivateKey) {
PrivateKey privateKey = null;
PKCS8EncodedKeySpec keySpec = new PKCS8EncodedKeySpec(Base64.getDecoder().decode(base64PrivateKey.getBytes()));
KeyFactory keyFactory = null;
try {
keyFactory = KeyFactory.getInstance("RSA");
} catch (NoSuchAlgorithmException e) {
e.printStackTrace();
}
try {
privateKey = keyFactory.generatePrivate(keySpec);
} catch (InvalidKeySpecException e) {
e.printStackTrace();
}
return privateKey;
}
public String encrypt(String data, PublicKey publicKey) throws BadPaddingException, IllegalBlockSizeException,
InvalidKeyException, NoSuchPaddingException, NoSuchAlgorithmException {
Cipher cipher = Cipher.getInstance("RSA/ECB/PKCS1Padding");
cipher.init(Cipher.ENCRYPT_MODE, publicKey);
return Base64.getEncoder().encodeToString(cipher.doFinal(data.getBytes()));
}
public String decrypt(String data, PrivateKey privateKey) throws NoSuchPaddingException,
NoSuchAlgorithmException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException {
Cipher cipher = Cipher.getInstance("RSA/ECB/PKCS1Padding");
cipher.init(Cipher.DECRYPT_MODE, privateKey);
return new String(cipher.doFinal(Base64.getDecoder().decode(data)));
}
现在我们可以实现这个类的俩个函数——加密的触发函数和需要选择数据时解密的函数：
package com.percona.blog.pljava;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.security.PrivateKey;
import java.security.PublicKey;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;
import org.postgresql.pljava.ResultSetProvider;
import org.postgresql.pljava.TriggerData;
public class CustomerCrypto implements ResultSetProvider {
private final String m_url = "jdbc:default:connection";
private final Connection conn;
private PreparedStatement stmt;
private ResultSet rs;
//
private PrivateKey privateKey;
private PublicKey publicKey;
public CustomerCrypto() throws SQLException, NoSuchAlgorithmException {
String query;
query = "SELECT * FROM keys WHERE id = 1";
conn = DriverManager.getConnection(m_url);
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
if (!rs.next())
throw new SQLException("Keys not found!");
privateKey = Crypto.getPrivateKey(rs.getString("priv"));
publicKey = Crypto.getPublicKey(rs.getString("pub"));
}
public void processQuery(int id) throws SQLException, NoSuchAlgorithmException {
String query;
query = "SELECT * FROM customer WHERE customer_id = ?";
stmt = conn.prepareStatement(query);
stmt.setInt(1, id);
rs = stmt.executeQuery();
}
@Override
public void close() throws SQLException {
stmt.close();
conn.close();
}
public static int getLineNumber() {
return Thread.currentThread().getStackTrace()[2].getLineNumber();
}
@Override
public boolean assignRowValues(ResultSet receiver, int currentRow) throws SQLException {
if (!rs.next())
return false;
try {
receiver.updateInt(1, rs.getInt("customer_id"));
receiver.updateInt(2, rs.getInt("store_id"));
receiver.updateString(3, Crypto.decrypt(rs.getString("first_name"), this.privateKey));
receiver.updateString(4, Crypto.decrypt(rs.getString("last_name"), this.privateKey));
receiver.updateString(5, Crypto.decrypt(rs.getString("email"), this.privateKey));
receiver.updateInt(6, rs.getInt("address_id"));
receiver.updateBoolean(7, rs.getBoolean("activebool"));
receiver.updateDate(8, rs.getDate("create_date"));
receiver.updateTimestamp(9, rs.getTimestamp("last_update"));
receiver.updateInt(10, rs.getInt("active"));
} catch (Exception e) {
Logger.getAnonymousLogger().log(Level.parse("SEVERE"), e.getMessage());
}
return true;
}
private void encryptData(TriggerData td) throws InvalidKeyException, BadPaddingException, IllegalBlockSizeException, NoSuchPaddingException, NoSuchAlgorithmException, SQLException {
ResultSet _new = td.getNew();
_new.updateString("first_name", Crypto.encrypt(_new.getString("first_name"), this.publicKey));
_new.updateString("last_name", Crypto.encrypt(_new.getString("last_name"), this.publicKey));
_new.updateString("email", Crypto.encrypt(_new.getString("email"), this.publicKey));
}
public static void customerBeforeInsertUpdate(TriggerData td) throws SQLException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException, NoSuchPaddingException, NoSuchAlgorithmException {
CustomerCrypto ret = new CustomerCrypto();
ret.encryptData(td);
}
public static ResultSetProvider getCustomerCrypto(int id) throws SQLException, NoSuchAlgorithmException {
CustomerCrypto ret = new CustomerCrypto();
ret.processQuery(id);
return ret;
}
}
上面代码的相关部分是 customerBeforeInsertUpdate 和 encryptData 方法，前者是数据库将访问的静态方法。Postgres 上的 PL/Java 期望找到一个带有void (TriggerData)签名的静态方法。它将调用 CustomerCrypto 对象的 encryptData 方法来完成这项工作。 encryptData 方法将从 TriggerData 对象传递的 NEW 指针恢复结果集，然后更改值以加密数据。我们需要在 BEFORE 事件中调用触发器，因为我们需要在它被持久化之前对其进行加密。
另一个重要的方法是“getCustomerCrypto”。我们需要能够获得解密数据，这种方法将对我们有所帮助。在这里，我们使用与上一个示例相同的技术，我们实现了“ResultSetProvider”接口并在返回结果集之前操作数据。仔细查看 assignRowValues 方法，您会发现我们在使用 Crypto.decrypt方法解密数据！ 好的，是时候编译代码并检查它是否真的有效：
javac -cp "/v01/proj/percona/blog/pljava/pljava-1_6_2/build/pljava-api-1.6.2.jar" com/percona/blog/pljava/*.java
jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/*.class
并创建数据库对象：
SELECT sqlj.replace_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );
CREATE FUNCTION customerBeforeInsertUpdate()
RETURNS trigger
AS 'com.percona.blog.pljava.CustomerCrypto.customerBeforeInsertUpdate'
LANGUAGE java;
CREATE TRIGGER tg_customerBeforeInsertUpdate
BEFORE INSERT ON customer
FOR EACH ROW
EXECUTE PROCEDURE customerBeforeInsertUpdate();
此时，我们的数据还没有加密，但我们可以通过 noop 更新来完成它，触发器会发挥它的魔力：
test=# SELECT * FROM customer LIMIT 3;
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | active
-------------+----------+------------+-----------+-------------------------------------+------------+------------+-------------+---------------------+--------
1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
(3 rows)
test=# UPDATE customer SET first_name = first_name, last_name = last_name, email = email;
UPDATE 599
test=# SELECT * FROM customer LIMIT 3;
customer_id | store_id |
first_name |
last_name
|
email
| address_id | activebool | create_date | last_update | active
-------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------+------------+------------+-------------+----------------------------+--------
3 | 1 | DT8oXb0VQvtFSIOv61zZfmUrpjWWGoeyl8D8tQl7naCLT31WlJn3U+uILYqUedSOdDSO17QdQKwChWG+DrcvYEih9RYyEPR2ja9deN4cn+vwHt/v09HDfmwrsJUt0UgP/fp78hCxkJDAV50KkMUsA23aeH5HRn9nCHOH0P
AcuId+7acCgwvU9YP8Sx2KVeVnLaBrzpeRLrsmAczQLUAXilXfdFC8uT2APBfwx1So2eCe+kSOsjcu1yTwlsa95Dnfu/N++Zm1D4knKUrAuNm5svTHjIz+B4HKXFMPz/Yk7KYF6ThB6OshIomyRvSEtKu5torfdwAvT3tsgP2DLWiKgQ== | H0YRoi10z36
tnNSXpBs/oYfMQRbAhfUYLIcE885Dhxmy2mbuhecCCqPcye5/++MhUwmEQG2pBgfsqWHLOnAgbqjaG3O0reipVysYK7cMysX1w5RVINsyD5H3vCqgnHESfdRuhW3b00InkR2qCtBYX1QJ1tKJZz89D2AOjoTq5jTum00vcLT06h6ZxVh1RKLNAuGpY9qN57m
/9a4JZuff9poYjw2PPQ6kTxhtbFl3bw+B3sJUcLFuFMYUoAAHsVETQRAerH1ncG9Uxi+xQjUtTVBqZdjvED+eydetH7vsnjBuYDtXD9XAn14qmALx5NfvwpU5jfpMOPOM4xP1BRVA2Q== | DpWBfhhii4LRPxZ9XJy8xoNne+qm051wD5Gd9AMHc+oIhx/B
ln6H+lcAM3625rKN1Vw/lG6VkQo2EnoZz/bhFtULvAOAUiBxerBDbYe0lYWqI50NxnFJbkexMkjDSiuoglh3ilRBn6Z+WGLc7FfEprOd1+tULW2gcwLI68uoEhfSY7INQZuGXfOUMAM4roB2fWvEfylL1ShbiGTRjX7KGXQbXLJtm7xel8J2VhdCecXzxzY2
Mtnu3EXGNpFy9atTXxE/fI0C5AX/u2FDZiOHz9xV7sB3atcqAeXwJB0smpBnPbwI3BN+ptzsWnhyFNNS+ol4QayMDgFhi/tp2+lCAQ== | 7 | t | 2006-02-14 | 2021-08-08 19:10:29.337653 | 1
4 | 2 | jo3zKr6lJ5zMN5f3/BPgENhs9CdzDu7F/uFxAf6uO9MAKc7X+++ipk/OBbvvA8vpaJ7DTgph9LshRvpYIqPMwS6IubkScDOSRDoLBNW9z2oMF3dB46R86LK0pTEVVrGaddjnPzaAEh7Uwzy3LncC1y7pJqGKW1b3RGUE8n
4SgstMo/4tRNUe/AUcPn9FXkCoc5jFvn8gPwVoTRKwhYu0oTco2gNKZs1rmFdmkmobGaMjZuhWyWG2PO1oXIVPkpgILmD42yAMDxWkS4DVvgJChXJRukjBzfOitsQwHapjqPqf/q3mfBaQzNUwROcSfGBe6KlI5yfjWU309KRKaCYWNQ== | MMhPovG/N3k
Xjou6kS9V7QtEvlA5NS8/n62KVRVVGEnsh5bhwEhBZxlK72AQv8e4niATWPEiJJU6i7Z08NkU5FWNIvuWwlGTdEEW+kK7XQXib6cNAdnmo4RH72SWhDxEp3tMwwoZif2932H8WDEbNdP6bCP69ekBA7Z+nGtXaeh+H9BAaLf1e6XunBj2YN7zs4sFWB2Kxs2
IugLWd9g9677BWzUeJIzpJfVLro4HYlzASh9AMKb8wPRU0LlEpxtqUdejj7IY5M1hVhDTCCLSQjSqJscqzG1pYQ04W7KNdGwWxJPMjvyPC2K4H+HQuW0IWVjvFpyYd/5q1eIQX+vjdw== | oF4nyIjtVtWuydg6QiSg3BDadWe48nhbBEZSLrR5GVigA768
E3n1npN6sdstzG7bRVnphtfwIZwQ3MUFURWCbUCe0VqioNgVXFhiRvr3DAw2AH64ss/h65B2U5whAygnq4kiy5JvPD0z0omtfs9913QeoO+ilvPVLEc0q3n0jD9ZQlkNVfHSytx1NY86gWnESquTVhkVQ55QDV8GY70YLX9V6nU7ldu+zpNLmf2+rfpxqbRC
i16jnHGDcTT7CKeq+AxbiJDeaaAmSPpxTZsrX4sXFW4rpNtSmOyuyHZziy8rkN8xSpyhvrmxjC7EYe4bn6L/+hay108Wn0BSFYe2ow== | 8 | t | 2006-02-14 | 2021-08-08 19:10:29.337653 | 1
<...>
(3 rows)
test=#
网友评论