PostgreSQL questions

To back up a PostgreSQL database, use the `pg_dump` utility. For example, to back up a database named 'mydb', you would run `pg_dump mydb > mydb_backup.sql`. This creates a SQL file with the database structure and data. You can restore this backup using the `psql` command with `psql mydb < mydb_backup.sql`.

To create a function in PostgreSQL, use the `CREATE FUNCTION` statement along with PL/pgSQL or another procedural language. For example: `CREATE FUNCTION get_employee_name(emp_id INT) RETURNS TEXT AS $$ BEGIN RETURN (SELECT name FROM employees WHERE id = emp_id); END; $$ LANGUAGE plpgsql;`. Use the function by calling `SELECT get_employee_name(1);`.

In PostgreSQL, large objects (LOBs) are handled using the `pg_largeobject` system catalog and associated functions. You can store large objects like files or images using `lo_create()`, `lo_write()`, and `lo_read()` functions. For example, to store a file: `SELECT lo_create(0);` to create a new large object, and then use `lo_write()` to write data. You can retrieve it with `lo_read()` and manage large objects using the `pg_largeobject` catalog.

PostgreSQL extensions add additional functionality to the database system. You can install extensions using the `CREATE EXTENSION` command. For example, to install the `pg_trgm` extension for trigram-based text search, use `CREATE EXTENSION pg_trgm;`. Extensions can be managed via the `pg_extension` catalog. Some extensions come with PostgreSQL distributions, while others may need to be downloaded and installed separately.