Prepared Statements

/**
 * Prepare a named statement asynchronously
 * @param statementName - Unique name for the prepared statement
 * @param text - SQL query text with $1, $2, etc. placeholders
 * @param nParams - Number of parameters in the query
 * @param callback - Callback function called when preparation completes
 */
client.prepare(
  statementName: string,
  text: string,
  nParams: number,
  callback: (err: Error | null) => void
): void;

const client = new Client();
client.connectSync();

// Prepare a simple select statement
client.prepare(
  'get_user_by_id',
  'SELECT name, email FROM users WHERE id = $1',
  1,
  function(err) {
    if (err) throw err;
    console.log('Statement prepared successfully');
  }
);

// Prepare an insert statement
client.prepare(
  'insert_user',
  'INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING id',
  3,
  function(err) {
    if (err) throw err;
    console.log('Insert statement prepared');
  }
);

// Prepare a complex query
client.prepare(
  'search_users',
  'SELECT * FROM users WHERE age BETWEEN $1 AND $2 AND city = $3 ORDER BY name',
  3,
  function(err) {
    if (err) throw err;
    console.log('Search statement prepared');
  }
);

/**
 * Prepare a named statement synchronously
 * @param statementName - Unique name for the prepared statement
 * @param text - SQL query text with $1, $2, etc. placeholders
 * @param nParams - Number of parameters in the query
 * @throws Error if preparation fails
 */
client.prepareSync(
  statementName: string,
  text: string,
  nParams: number
): void;

const client = new Client();
client.connectSync();

try {
  // Prepare statements synchronously
  client.prepareSync(
    'get_user_by_id',
    'SELECT name, email FROM users WHERE id = $1',
    1
  );

  client.prepareSync(
    'update_user_age',
    'UPDATE users SET age = $1 WHERE id = $2',
    2
  );

  console.log('All statements prepared successfully');
} catch (err) {
  console.error('Statement preparation failed:', err.message);
}

/**
 * Execute a prepared statement asynchronously
 * @param statementName - Name of the prepared statement
 * @param parameters - Array of parameter values
 * @param callback - Callback function receiving results
 */
client.execute(
  statementName: string,
  parameters: any[],
  callback: (err: Error | null, rows: any[], results?: any) => void
): void;

const client = new Client();
client.connectSync();

// Prepare a statement first
client.prepareSync(
  'get_user_by_id',
  'SELECT name, email FROM users WHERE id = $1',
  1
);

// Execute the prepared statement multiple times
client.execute('get_user_by_id', [1], function(err, rows) {
  if (err) throw err;
  console.log('User 1:', rows[0]);
});

client.execute('get_user_by_id', [2], function(err, rows) {
  if (err) throw err;
  console.log('User 2:', rows[0]);
});

// Execute with different parameter types
client.prepareSync(
  'search_users',
  'SELECT * FROM users WHERE age > $1 AND active = $2',
  2
);

client.execute('search_users', [25, true], function(err, rows) {
  if (err) throw err;
  console.log('Active users over 25:', rows);
});

/**
 * Execute a prepared statement synchronously
 * @param statementName - Name of the prepared statement
 * @param parameters - Array of parameter values
 * @returns Array of result rows
 * @throws Error if execution fails
 */
client.executeSync(statementName: string, parameters: any[]): any[];

const client = new Client();
client.connectSync();

// Prepare statements
client.prepareSync(
  'get_user_by_id',
  'SELECT name, email FROM users WHERE id = $1',
  1
);

client.prepareSync(
  'insert_user',
  'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id',
  2
);

try {
  // Execute select statement
  const user = client.executeSync('get_user_by_id', [1]);
  console.log('Retrieved user:', user[0]);

  // Execute insert statement
  const insertResult = client.executeSync('insert_user', ['Alice', 'alice@example.com']);
  console.log('New user ID:', insertResult[0].id);

  // Execute multiple times with different parameters
  const users = [
    ['Bob', 'bob@example.com'],
    ['Charlie', 'charlie@example.com']
  ];

  users.forEach(userData => {
    const result = client.executeSync('insert_user', userData);
    console.log('Inserted user ID:', result[0].id);
  });

} catch (err) {
  console.error('Statement execution failed:', err.message);
}

const Client = require('pg-native');

const client = new Client();
client.connectSync();

// Prepare multiple statements for a user management system
client.prepareSync(
  'create_user',
  'INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING id, created_at',
  3
);

client.prepareSync(
  'get_user',
  'SELECT id, name, email, age, created_at FROM users WHERE id = $1',
  1
);

client.prepareSync(
  'update_user',
  'UPDATE users SET name = $1, email = $2, age = $3 WHERE id = $4 RETURNING *',
  4
);

client.prepareSync(
  'delete_user',
  'DELETE FROM users WHERE id = $1',
  1
);

try {
  // Create a new user
  const newUser = client.executeSync('create_user', ['John Doe', 'john@example.com', 30]);
  const userId = newUser[0].id;
  console.log('Created user:', newUser[0]);

  // Retrieve the user
  const retrievedUser = client.executeSync('get_user', [userId]);
  console.log('Retrieved user:', retrievedUser[0]);

  // Update the user
  const updatedUser = client.executeSync('update_user', [
    'John Smith',
    'johnsmith@example.com',
    31,
    userId
  ]);
  console.log('Updated user:', updatedUser[0]);

  // Delete the user
  client.executeSync('delete_user', [userId]);
  console.log('User deleted');

} catch (err) {
  console.error('Operation failed:', err.message);
} finally {
  client.end();
}