[Subqueries] Handle pl/pgsql invocations in the `FROM` clause by executing them as a subquery

[hanjianqiao]

PgDog version
ghcr.io/pgdogdev/pgdog:latest

Description
When inserting 200 data entries using the generate_series function, 200 data entries were generated for each shard, which was intended to distribute these 200 data entries evenly across the shards.

Logs

# using pgdog with two shards
CREATE TABLE orders (order_id bigint, customer_id bigint, amount numeric(10,2), PRIMARY KEY (order_id, customer_id));
INSERT INTO orders SELECT g, g, (g*1.5) FROM generate_series(1,200) g;

After previous query is done, via pgdog select count(*) from orders; returns 400, and each shard has 200 same records.

Configuration
pgdog.shard.toml

[general]
host = "0.0.0.0"
port = 6432
default_pool_size = 10

[[databases]]
name = "data_shard"
host = "data-shard0"
port = 5432
database_name = "testdb"
shard = 0

[[databases]]
name = "data_shard"
host = "data-shard1"
port = 5432
database_name = "testdb"
shard = 1

[[sharded_tables]]
database = "data_shard"
column = "customer_id"

[levkk]

Yup, we need to evaluate and expand this function inside PgDog. Currently, we send it as-is to Postgres.

[levkk]

Actually, correction, we should handle this as a subquery:

1. Send it to the right shard
2. Collect the result
3. Inject it into the top level query and send it to Postgres