AI Agents & MCPs & AI Workflow Automation • (~400 MCP servers for AI agents) • AI Automation / AI Agent with MCPs • AI Workflows & AI Agents • MCPs for AI Agents
TypeScript ai-agentai-agent-toolsai-agentsai-agents-frameworkmcpmcp-servermcp-toolsmcpsn8n-alternativeno-code-automationworkflowworkflow-automationworkflows
import { MarkdownVariant, Property } from "@activepieces/pieces-framework";
import { postgresAuth } from "./auth";
import { Client } from "pg";
import { pgClient } from "./client";
import format from "pg-format";
const listTables = async (client: Client): Promise<PostgresTable[]> => {
const result = await client.query<PostgresTable>(
`SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name`
);
return result.rows;
}
const listColumns = async ({ client, table }: { client: Client, table: PostgresTable }): Promise<string[]> => {
const result = await client.query<{ column_name: string }>(
`SELECT column_name FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2 ORDER BY ordinal_position`,
[table.table_schema, table.table_name]
);
return result.rows.map((row) => row.column_name);
}
const listColumnDetails = async ({ client, table }: { client: Client, table: PostgresTable }): Promise<PostgresColumn[]> => {
const result = await client.query<PostgresColumn>(
`SELECT column_name, data_type, is_nullable, column_default, is_identity, is_generated FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2 ORDER BY ordinal_position`,
[table.table_schema, table.table_name]
);
return result.rows;
}
const isPostgresTable = (value: unknown): value is PostgresTable => {
if (typeof value !== 'object' || value === null) {
return false;
}
const candidate: Record<string, unknown> = { ...value };
return typeof candidate['table_schema'] === 'string' && typeof candidate['table_name'] === 'string';
}
const qualifiedName = (table: PostgresTable): string => format('%I.%I', table.table_schema, table.table_name);
const quoteIdentifier = (name: string): string => name === '*' ? name : format.ident(name);
export { pgClient };
export const warningMarkdown = Property.MarkDown({
value: 'Never type dynamic values into the condition. Write $1, $2 and put the values in Arguments to prevent SQL injection.',
variant: MarkdownVariant.WARNING,
});
export const postgresCommon = {
table: Property.Dropdown<PostgresTable, true, typeof postgresAuth>({
auth: postgresAuth,
displayName: 'Table',
required: true,
refreshers: [],
refreshOnSearch: false,
options: async ({ auth }) => {
if (!auth) {
return {
disabled: true,
options: [],
placeholder: 'Please authenticate first',
};
}
const client = await pgClient(auth);
try {
const tables = await listTables(client);
return {
disabled: false,
options: tables.map((table) => ({
label: `${table.table_schema}.${table.table_name}`,
value: {
table_schema: table.table_schema,
table_name: table.table_name,
},
})),
};
} finally {
await client.end();
}
},
}),
column: ({ displayName, description }: { displayName: string, description?: string }) =>
Property.Dropdown<string, true, typeof postgresAuth>({
auth: postgresAuth,
displayName,
description,
required: true,
refreshers: ['table'],
refreshOnSearch: false,
options: async ({ auth, table }) => {
if (!auth) {
return {
disabled: true,
options: [],
placeholder: 'Please authenticate first',
};
}
if (!isPostgresTable(table)) {
return {
disabled: true,
options: [],
placeholder: 'Please select a table',
};
}
const client = await pgClient(auth);
try {
const columns = await listColumns({ client, table });
return {
disabled: false,
options: columns.map((column) => ({
label: column,
value: column,
})),
};
} finally {
await client.end();
}
},
}),
columns: Property.MultiSelectDropdown<string, false, typeof postgresAuth>({
auth: postgresAuth,
displayName: 'Columns',
description: 'Leave empty to return every column.',
required: false,
refreshers: ['table'],
options: async ({ auth, table }) => {
if (!auth) {
return {
disabled: true,
options: [],
placeholder: 'Please authenticate first',
};
}
if (!isPostgresTable(table)) {
return {
disabled: true,
options: [],
placeholder: 'Please select a table',
};
}
const client = await pgClient(auth);
try {
const columns = await listColumns({ client, table });
return {
disabled: false,
options: columns.map((column) => ({
label: column,
value: column,
})),
};
} finally {
await client.end();
}
},
}),
};
export const postgresUtils = {
listTables,
listColumns,
listColumnDetails,
isPostgresTable,
qualifiedName,
quoteIdentifier,
};
export type PostgresTable = {
table_schema: string;
table_name: string;
};
export type PostgresColumn = {
column_name: string;
data_type: string;
is_nullable: string;
column_default: string | null;
is_identity: string;
is_generated: string;
};