Cline

# Database: Create functions You're a Supabase Postgres expert in writing database functions. Generate **high-quality PostgreSQL functions** that adhere to the following best practices: ## General Guidelines 1. **Default to `SECURITY INVOKER`:** - Functions should run with the permissions of the user invoking the function, ensuring safer access control. - Use `SECURITY DEFINER` only when explicitly required and explain the rationale. 2. **Set the `search_path` Configuration Parameter:** - Always set `search_path` to an empty string (`set search_path = '';`). - This avoids unexpected behavior and security risks caused by resolving object references in untrusted or unintended schemas. - Use fully qualified names (e.g., `schema_name.table_name`) for all database objects referenced within the function. 3. **Adhere to SQL Standards and Validation:** - Ensure all queries within the function are valid PostgreSQL SQL queries and compatible with the specified context (ie. Supabase). ## Best Practices 1. **Minimize Side Effects:** - Prefer functions that return results over those that modify data unless they serve a specific purpose (e.g., triggers). 2. **Use Explicit Typing:** - Clearly specify input and output types, avoiding ambiguous or loosely typed parameters. 3. **Default to Immutable or Stable Functions:** - Where possible, declare functions as `IMMUTABLE` or `STABLE` to allow better optimization by PostgreSQL. Use `VOLATILE` only if the function modifies data or has side effects. 4. **Triggers (if Applicable):** - If the function is used as a trigger, include a valid `CREATE TRIGGER` statement that attaches the function to the desired table and event (e.g., `BEFORE INSERT`). ## Example Templates ### Simple Function with `SECURITY INVOKER` ```sql create or replace function my_schema.hello_world() returns text language plpgsql security invoker set search_path = '' as $$ begin return 'hello world'; end; $$; ``` ### Function with Parameters and Fully Qualified Object Names ```sql create or replace function public.calculate_total_price(order_id bigint) returns numeric language plpgsql security invoker set search_path = '' as $$ declare total numeric; begin select sum(price * quantity) into total from public.order_items where order_id = calculate_total_price.order_id; return total; end; $$; ``` ### Function as a Trigger ```sql create or replace function my_schema.update_updated_at() returns trigger language plpgsql security invoker set search_path = '' as $$ begin -- Update the "updated_at" column on row modification new.updated_at := now(); return new; end; $$; create trigger update_updated_at_trigger before update on my_schema.my_table for each row execute function my_schema.update_updated_at(); ``` ### Function with Error Handling ```sql create or replace function my_schema.safe_divide(numerator numeric, denominator numeric) returns numeric language plpgsql security invoker set search_path = '' as $$ begin if denominator = 0 then raise exception 'Division by zero is not allowed'; end if; return numerator / denominator; end; $$; ``` ### Immutable Function for Better Optimization ```sql create or replace function my_schema.full_name(first_name text, last_name text) returns text language sql security invoker set search_path = '' immutable as $$ select first_name || ' ' || last_name; $$; ```

# Database: Create RLS policies You're a Supabase Postgres expert in writing row level security policies. Your purpose is to generate a policy with the constraints given by the user. You should first retrieve schema information to write policies for, usually the 'public' schema. The output should use the following instructions: - The generated SQL must be valid SQL. - You can use only CREATE POLICY or ALTER POLICY queries, no other queries are allowed. - Always use double apostrophe in SQL strings (eg. 'Night''s watch') - You can add short explanations to your messages. - The result should be a valid markdown. The SQL code should be wrapped in ``` (including sql language tag). - Always use "auth.uid()" instead of "current_user". - SELECT policies should always have USING but not WITH CHECK - INSERT policies should always have WITH CHECK but not USING - UPDATE policies should always have WITH CHECK and most often have USING - DELETE policies should always have USING but not WITH CHECK - Don't use `FOR ALL`. Instead separate into 4 separate policies for select, insert, update, and delete. - The policy name should be short but detailed text explaining the policy, enclosed in double quotes. - Always put explanations as separate text. Never use inline SQL comments. - If the user asks for something that's not related to SQL policies, explain to the user that you can only help with policies. - Discourage `RESTRICTIVE` policies and encourage `PERMISSIVE` policies, and explain why. The output should look like this: ```sql CREATE POLICY "My descriptive policy." ON books FOR INSERT to authenticated USING ( (select auth.uid()) = author_id ) WITH ( true ); ``` Since you are running in a Supabase environment, take note of these Supabase-specific additions below. ## Authenticated and unauthenticated roles Supabase maps every request to one of the roles: - `anon`: an unauthenticated request (the user is not logged in) - `authenticated`: an authenticated request (the user is logged in) These are actually [Postgres Roles](/docs/guides/database/postgres/roles). You can use these roles within your Policies using the `TO` clause: ```sql create policy "Profiles are viewable by everyone" on profiles for select to authenticated, anon using ( true ); -- OR create policy "Public profiles are viewable only by authenticated users" on profiles for select to authenticated using ( true ); ``` Note that `for ...` must be added after the table but before the roles. `to ...` must be added after `for ...`: ### Incorrect ```sql create policy "Public profiles are viewable only by authenticated users" on profiles to authenticated for select using ( true ); ``` ### Correct ```sql create policy "Public profiles are viewable only by authenticated users" on profiles for select to authenticated using ( true ); ``` ## Multiple operations PostgreSQL policies do not support specifying multiple operations in a single FOR clause. You need to create separate policies for each operation. ### Incorrect ```sql create policy "Profiles can be created and deleted by any user" on profiles for insert, delete -- cannot create a policy on multiple operators to authenticated with check ( true ) using ( true ); ``` ### Correct ```sql create policy "Profiles can be created by any user" on profiles for insert to authenticated with check ( true ); create policy "Profiles can be deleted by any user" on profiles for delete to authenticated using ( true ); ``` ## Helper functions Supabase provides some helper functions that make it easier to write Policies. ### `auth.uid()` Returns the ID of the user making the request. ### `auth.jwt()` Returns the JWT of the user making the request. Anything that you store in the user's `raw_app_meta_data` column or the `raw_user_meta_data` column will be accessible using this function. It's important to know the distinction between these two: - `raw_user_meta_data` - can be updated by the authenticated user using the `supabase.auth.update()` function. It is not a good place to store authorization data. - `raw_app_meta_data` - cannot be updated by the user, so it's a good place to store authorization data. The `auth.jwt()` function is extremely versatile. For example, if you store some team data inside `app_metadata`, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs: ```sql create policy "User is in team" on my_table to authenticated using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams')); ``` ### MFA The `auth.jwt()` function can be used to check for [Multi-Factor Authentication](/docs/guides/auth/auth-mfa#enforce-rules-for-mfa-logins). For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2): ```sql create policy "Restrict updates." on profiles as restrictive for update to authenticated using ( (select auth.jwt()->>'aal') = 'aal2' ); ``` ## RLS performance recommendations Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many `select` operations, including those using limit, offset, and ordering. Based on a series of [tests](https://github.com/GaryAustin1/RLS-Performance), we have a few recommendations for RLS: ### Add indexes Make sure you've added [indexes](/docs/guides/database/postgres/indexes) on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this: ```sql create policy "Users can access their own records" on test_table to authenticated using ( (select auth.uid()) = user_id ); ``` You can add an index like: ```sql create index userid on test_table using btree (user_id); ``` ### Call functions with `select` You can use `select` statement to improve policies that use functions. For example, instead of this: ```sql create policy "Users can access their own records" on test_table to authenticated using ( auth.uid() = user_id ); ``` You can do: ```sql create policy "Users can access their own records" on test_table to authenticated using ( (select auth.uid()) = user_id ); ``` This method works well for JWT functions like `auth.uid()` and `auth.jwt()` as well as `security definer` Functions. Wrapping the function causes an `initPlan` to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row. Caution: You can only use this technique if the results of the query or function do not change based on the row data. ### Minimize joins You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an `IN` or `ANY` operation in your filter. For example, this is an example of a slow policy which joins the source `test_table` to the target `team_user`: ```sql create policy "Users can access records belonging to their teams" on test_table to authenticated using ( (select auth.uid()) in ( select user_id from team_user where team_user.team_id = team_id -- joins to the source "test_table.team_id" ) ); ``` We can rewrite this to avoid this join, and instead select the filter criteria into a set: ```sql create policy "Users can access records belonging to their teams" on test_table to authenticated using ( team_id in ( select team_id from team_user where user_id = (select auth.uid()) -- no join ) ); ``` ### Specify roles in your policies Always use the Role of inside your policies, specified by the `TO` operator. For example, instead of this query: ```sql create policy "Users can access their own records" on rls_test using ( auth.uid() = user_id ); ``` Use: ```sql create policy "Users can access their own records" on rls_test to authenticated using ( (select auth.uid()) = user_id ); ``` This prevents the policy `( (select auth.uid()) = user_id )` from running for any `anon` users, since the execution stops at the `to authenticated` step.

# Postgres SQL Style Guide ## General - Use lowercase for SQL reserved words to maintain consistency and readability. - Employ consistent, descriptive identifiers for tables, columns, and other database objects. - Use white space and indentation to enhance the readability of your code. - Store dates in ISO 8601 format (`yyyy-mm-ddThh:mm:ss.sssss`). - Include comments for complex logic, using '/_ ... _/' for block comments and '--' for line comments. ## Naming Conventions - Avoid SQL reserved words and ensure names are unique and under 63 characters. - Use snake_case for tables and columns. - Prefer plurals for table names - Prefer singular names for columns. ## Tables - Avoid prefixes like 'tbl\_' and ensure no table name matches any of its column names. - Always add an `id` column of type `identity generated always` unless otherwise specified. - Create all tables in the `public` schema unless otherwise specified. - Always add the schema to SQL queries for clarity. - Always add a comment to describe what the table does. The comment can be up to 1024 characters. ## Columns - Use singular names and avoid generic names like 'id'. - For references to foreign tables, use the singular of the table name with the `_id` suffix. For example `user_id` to reference the `users` table - Always use lowercase except in cases involving acronyms or when readability would be enhanced by an exception. #### Examples: ```sql create table books ( id bigint generated always as identity primary key, title text not null, author_id bigint references authors (id) ); comment on table books is 'A list of all the books in the library.'; ``` ## Queries - When the query is shorter keep it on just a few lines. As it gets larger start adding newlines for readability - Add spaces for readability. Smaller queries: ```sql select * from employees where end_date is null; update employees set end_date = '2023-12-31' where employee_id = 1001; ``` Larger queries: ```sql select first_name, last_name from employees where start_date between '2021-01-01' and '2021-12-31' and status = 'employed'; ``` ### Joins and Subqueries - Format joins and subqueries for clarity, aligning them with related SQL clauses. - Prefer full table names when referencing tables. This helps for readability. ```sql select employees.employee_name, departments.department_name from employees join departments on employees.department_id = departments.department_id where employees.start_date > '2022-01-01'; ``` ## Aliases - Use meaningful aliases that reflect the data or transformation applied, and always include the 'as' keyword for clarity. ```sql select count(*) as total_employees from employees where end_date is null; ``` ## Complex queries and CTEs - If a query is extremely complex, prefer a CTE. - Make sure the CTE is clear and linear. Prefer readability over performance. - Add comments to each block. ```sql with department_employees as ( -- Get all employees and their departments select employees.department_id, employees.first_name, employees.last_name, departments.department_name from employees join departments on employees.department_id = departments.department_id ), employee_counts as ( -- Count how many employees in each department select department_name, count(*) as num_employees from department_employees group by department_name ) select department_name, num_employees from employee_counts order by department_name; ```

# Writing Supabase Edge Functions You're an expert in writing TypeScript and Deno JavaScript runtime. Generate **high-quality Supabase Edge Functions** that adhere to the following best practices: ## Guidelines 1. Try to use Web APIs and Deno’s core APIs instead of external dependencies (eg: use fetch instead of Axios, use WebSockets API instead of node-ws) 2. If you are reusing utility methods between Edge Functions, add them to `supabase/functions/_shared` and import using a relative path. Do NOT have cross dependencies between Edge Functions. 3. Do NOT use bare specifiers when importing dependecnies. If you need to use an external dependency, make sure it's prefixed with either `npm:` or `jsr:`. For example, `@supabase/supabase-js` should be written as `npm:@supabase/supabase-js`. 4. For external imports, always define a version. For example, `npm:@express` should be written as `npm:express@4.18.2`. 5. For external dependencies, importing via `npm:` and `jsr:` is preferred. Minimize the use of imports from @`deno.land/x` , `esm.sh` and @`unpkg.com` . If you have a package from one of those CDNs, you can replace the CDN hostname with `npm:` specifier. 6. You can also use Node built-in APIs. You will need to import them using `node:` specifier. For example, to import Node process: `import process from "node:process". Use Node APIs when you find gaps in Deno APIs. 7. Do NOT use `import { serve } from "https://deno.land/std@0.168.0/http/server.ts"`. Instead use the built-in `Deno.serve`. 8. Following environment variables (ie. secrets) are pre-populated in both local and hosted Supabase environments. Users don't need to manually set them: - SUPABASE_URL - SUPABASE_ANON_KEY - SUPABASE_SERVICE_ROLE_KEY - SUPABASE_DB_URL 9. To set other environment variables (ie. secrets) users can put them in a env file and run the `supabase secrets set --env-file path/to/env-file` 10. A single Edge Function can handle multiple routes. It is recommended to use a library like Express or Hono to handle the routes as it's easier for developer to understand and maintain. Each route must be prefixed with `/function-name` so they are routed correctly. 11. File write operations are ONLY permitted on `/tmp` directory. You can use either Deno or Node File APIs. 12. Use `EdgeRuntime.waitUntil(promise)` static method to run long-running tasks in the background without blocking response to a request. Do NOT assume it is available in the request / execution context. ## Example Templates ### Simple Hello World Function ```tsx interface reqPayload { name: string } console.info('server started') Deno.serve(async (req: Request) => { const { name }: reqPayload = await req.json() const data = { message: `Hello ${name} from foo!`, } return new Response(JSON.stringify(data), { headers: { 'Content-Type': 'application/json', Connection: 'keep-alive' }, }) }) ``` ### Example Function using Node built-in API ```tsx import { randomBytes } from 'node:crypto' import { createServer } from 'node:http' import process from 'node:process' const generateRandomString = (length) => { const buffer = randomBytes(length) return buffer.toString('hex') } const randomString = generateRandomString(10) console.log(randomString) const server = createServer((req, res) => { const message = `Hello` res.end(message) }) server.listen(9999) ``` ### Using npm packages in Functions ```tsx import express from 'npm:express@4.18.2' const app = express() app.get(/(.*)/, (req, res) => { res.send('Welcome to Supabase') }) app.listen(8000) ``` ### Generate embeddings using built-in @Supabase.ai API ```tsx const model = new Supabase.ai.Session('gte-small') Deno.serve(async (req: Request) => { const params = new URL(req.url).searchParams const input = params.get('text') const output = await model.run(input, { mean_pool: true, normalize: true }) return new Response(JSON.stringify(output), { headers: { 'Content-Type': 'application/json', Connection: 'keep-alive', }, }) }) ```

# API Integration Standards for PostgreSQL This document outlines the coding standards for integrating PostgreSQL with external APIs and backend services. These standards promote maintainability, performance, and security when building applications that rely on data and functionality outside of the database itself. It focuses on modern approaches compatible with the latest PostgreSQL version. ## 1. Architectural Considerations for API Integration ### 1.1. Standard: Define Clear API Boundaries **Do This:** * Clearly define the responsibilities of PostgreSQL and external APIs. Use PostgreSQL for data persistence, relational logic, and indexing. Offload complex computations, specialized data processing, and external data access to APIs. * Use clear and consistent naming conventions for database functions/procedures interacting with APIs. Prefix them (e.g., "api_", "ext_") to easily identify external API integration code. * Document the contract (input/output) with each API thoroughly. **Don't Do This:** * Overload PostgreSQL with tasks that APIs are better suited for (e.g., image processing, complex machine learning tasks that are not data-intensive). * Embed undocumented or magic API calls directly within SQL queries. **Why:** Defining clear boundaries ensures modularity, easier maintenance, and optimized performance. It avoids turning the database into a monolithic application component. **Example:** """sql -- Good: Function for fetching user profiles from an external API. CREATE OR REPLACE FUNCTION api_get_user_profile(user_id INT) RETURNS JSONB AS $$ BEGIN -- Call external API to get user profile details. -- Using a hypothetical extension for API calls RETURN http_get('https://api.example.com/users/' || user_id)::jsonb; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error fetching user profile from API: %', SQLERRM; END; $$ LANGUAGE plpgsql; -- Bad: Embedding API logic directly within a complex query. -- SELECT * FROM users WHERE ... AND api_call(...) ... ; -- Avoid! """ ### 1.2. Standard: Asynchronous vs. Synchronous API Interactions **Do This:** * Use asynchronous API calls (e.g., message queues, background workers) where possible to prevent long-running database transactions from blocking other operations. Implement retries and error handling for asynchronous tasks. * For synchronous calls, keep the execution time as short as possible to avoid holding database connections for extended periods. **Don't Do This:** * Make blocking API calls directly within critical transaction paths. This will significantly impact database performance and availability. * Assume API calls will always succeed. Implement robust error handling and retries. **Why:** Asynchronous operations improve scalability and responsiveness. Synchronous operations can lead to deadlocks and performance degradation if not managed carefully. **Example (using pg_amqp or similar queue extensions):** """sql -- Asynchronous API call using a message queue. (Hypothetical Example) CREATE OR REPLACE FUNCTION api_process_user_data(user_id INT) RETURNS VOID AS $$ BEGIN -- Send a message to a queue for processing user data via an external API. PERFORM amqp.publish('process_user_data_queue', json_build_object('user_id', user_id)); -- Hypothetical RETURN; END; $$ LANGUAGE plpgsql; -- Example of a background worker (using pg_background) that consumes from the queue to call the external API -- Code for the background worker would be in a separate file and process the queue. """ ### 1.3. Standard: Data Transformation and Mapping **Do This:** * Define clear data mapping between PostgreSQL data types and API request/response formats (e.g., JSON, XML). Use PostgreSQL's JSONB and XML support effectively. * Validate data received from APIs before inserting it into the database using "CHECK" constraints or other validation mechanisms. * Log API requests and responses for debugging and auditing purposes. **Don't Do This:** * Directly insert untrusted data received from APIs into the database without validation. This can lead to SQL injection and other security vulnerabilities. * Rely on implicit type conversions between PostgreSQL and API data formats. Be explicit. **Why:** Proper data transformation and validation prevent data corruption and security breaches. Logging helps troubleshoot issues and track API usage. **Example:** """sql -- Validating and inserting JSON data from an API. CREATE TABLE api_user_profiles ( user_id INT PRIMARY KEY, profile_data JSONB -- CHECK constraint is appropriate here to require the JSON object ALWAYS conform to a schema ); CREATE OR REPLACE FUNCTION api_import_user_profile(user_id INT, profile_json JSONB) RETURNS VOID AS $$ DECLARE -- Validate JSON data against a schema (hypothetical function). is_valid BOOLEAN; BEGIN -- Validate that the JSON is valid against a schema is_valid := jsonb_matches_schema('{"type": "object", "properties": {"name": {"type": "string"},"email": {"type": "string", "format": "email"} }}', profile_json); IF NOT is_valid THEN RAISE EXCEPTION 'Invalid profile data format.'; END IF; INSERT INTO api_user_profiles (user_id, profile_data) VALUES (user_id, profile_json); RETURN; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error importing user profile: %', SQLERRM; END; $$ LANGUAGE plpgsql; """ ## 2. Implementation Details ### 2.1. Standard: Choosing the Right API Interaction Method **Do This:** * Evaluate these methods: * **HTTP Requests (using extensions like "http" or "curl"):** Suitable for RESTful APIs. * **Message Queues (using extensions like "pg_amqp" or "pg_kafka"):** Ideal for asynchronous communication. * **Foreign Data Wrappers (FDWs):** For integrating with other databases or data stores directly. * Choose the method that best fits the API's protocol, data format, and communication pattern. **Don't Do This:** * Force a specific integration method because it's familiar. Consider alternatives based on the API's characteristics. * Build custom, ad-hoc solutions when standard extensions and FDWs provide the necessary functionality. **Why:** Selecting the right method simplifies integration, improves performance, and reduces development effort. **Example (using "http" extension for a REST API):** """sql -- Example using the http extension to call a REST API CREATE EXTENSION IF NOT EXISTS http; CREATE OR REPLACE FUNCTION api_get_weather(city TEXT) RETURNS JSONB AS $$ DECLARE api_url TEXT := 'https://api.weatherapi.com/v1/current.json?key=YOUR_API_KEY&q=' || city; response HTTPResponse; BEGIN response := http_get(api_url); IF response.status_code = 200 THEN RETURN response.content::jsonb; ELSE RAISE EXCEPTION 'Weather API error: %', response.content; END IF; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error fetching weather data: %', SQLERRM; END; $$ LANGUAGE plpgsql; -- SELECT api_get_weather('London'); """ ### 2.2. Standard: Error Handling and Retries **Do This:** * Implement robust error handling for API calls. Catch exceptions, log errors, and implement retry mechanisms with exponential backoff. * Distinguish between transient and permanent errors. Retry transient errors (e.g., network timeouts), and log permanent errors (e.g., invalid API key) for investigation. * Set appropriate timeouts for API calls to prevent indefinite blocking. * Consider using "TRY...CATCH" blocks for error handling within PL/pgSQL functions. **Don't Do This:** * Ignore errors from API calls. At a minimum, log the error so it can be investigated later. * Retry indefinitely without a limit or backoff strategy. This can overload the API or the database. **Why:** Robust error handling ensures resilience and prevents cascading failures. It provides valuable insights into API issues. **Example:** """sql CREATE OR REPLACE FUNCTION api_get_data_with_retry(url TEXT, max_retries INT DEFAULT 3) RETURNS JSONB AS $$ DECLARE response HTTPResponse; retries INT := 0; delay INTERVAL := '1 second'; BEGIN LOOP BEGIN response := http_get(url); IF response.status_code = 200 THEN RETURN response.content::jsonb; ELSE RAISE WARNING 'API call failed with status code: %', response.status_code; -- Check for non-retryable errors here! -- IF response.status_code = 400 THEN RETURN NULL; -- Bad Request (do not retry) END IF; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'API call error: %', SQLERRM; END; retries := retries + 1; IF retries >= max_retries THEN RAISE EXCEPTION 'Max retries exceeded for API call.'; END IF; RAISE NOTICE 'Retrying in %', delay; PERFORM pg_sleep(extract(epoch from delay)); delay := delay * 2; -- Exponential backoff END LOOP; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Failed to get data after multiple retries: %', SQLERRM; END; $$ LANGUAGE plpgsql; """ ### 2.3. Standard: Security Considerations **Do This:** * Store API keys and secrets securely using PostgreSQL's configuration parameters or a dedicated secrets management solution. NEVER hardcode API keys in SQL code. * Use HTTPS for all API calls to encrypt data in transit. * Validate API responses to prevent data injection (e.g., JSON injection). * Implement rate limiting to prevent abuse. * Use least privilege principle when granting permissions to API interaction functions. **Don't Do This:** * Hardcode API keys or secrets in SQL code or store them in plain text in the database. * Trust API responses implicitly. Always validate the data. * Expose your PostgreSQL database directly to the internet without proper firewall and security measures. **Why:** Security is paramount. Protecting API keys, encrypting data, and rate limiting prevent unauthorized access and malicious attacks. **Example:** """sql -- Storing API key securely using postgresql.conf -- In postgresql.conf: -- api.weather_api_key = 'YOUR_API_KEY' -- SQL to retrieve the API key CREATE OR REPLACE FUNCTION api_get_weather_secure(city TEXT) RETURNS JSONB AS $$ DECLARE api_url TEXT := 'https://api.weatherapi.com/v1/current.json?key=' || current_setting('api.weather_api_key') || '&q=' || city; response HTTPResponse; BEGIN response := http_get(api_url); IF response.status_code = 200 THEN RETURN response.content::jsonb; ELSE RAISE EXCEPTION 'Weather API error: %', response.content; END IF; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error fetching weather data: %', SQLERRM; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- SECURITY DEFINER crucial for accessing external configurations -- Revoke execute permission from public REVOKE EXECUTE ON FUNCTION api_get_weather_secure(TEXT) FROM PUBLIC; -- Grant access to specific roles GRANT EXECUTE ON FUNCTION api_get_weather_secure(TEXT) TO your_application_role; """ ### 2.4. Standard: Performance Optimization **Do This:** * Cache API responses to reduce the number of API calls, especially for frequently accessed data. Use "MATERIALIZED VIEW" or a custom cache table. * Use connection pooling to minimize the overhead of establishing new connections to APIs. Some HTTP extensions do this internally. * Optimize data transfer by requesting only the necessary fields from the API. Use appropriate query parameters. **Don't Do This:** * Make redundant API calls. Identify opportunities for caching or batching. * Retrieve large amounts of data from APIs when only a small subset is needed. **Why:** Performance optimization improves application responsiveness and reduces API usage costs. **Example (using a materialized view for caching):** """sql CREATE MATERIALIZED VIEW weather_cache AS SELECT city, api_get_weather(city) AS weather_data, NOW() AS last_updated FROM (VALUES ('London'), ('New York'), ('Tokyo')) AS cities(city); CREATE UNIQUE INDEX idx_weather_cache_city ON weather_cache (city); -- Refresh the cache periodically CREATE OR REPLACE FUNCTION refresh_weather_cache() RETURNS VOID AS $$ BEGIN REFRESH MATERIALIZED VIEW CONCURRENTLY weather_cache; RETURN; END; $$ LANGUAGE plpgsql; -- Schedule daily refreshes with pg_cron or a similar scheduler: -- SELECT cron.schedule('0 0 * * *', 'SELECT refresh_weather_cache()'); -- Usage: CREATE OR REPLACE FUNCTION get_weather_from_cache(city TEXT) RETURNS JSONB AS $$ BEGIN RETURN (SELECT weather_data FROM weather_cache WHERE city = get_weather_from_cache.city); EXCEPTION WHEN no_data_found THEN RETURN api_get_weather(city); -- if not in cache, fetch it from the API END; $$ LANGUAGE plpgsql; """ ## 3. Coding Style and Conventions ### 3.1. Standard: Code Formatting and Comments **Do This:** * Use consistent indentation (typically 4 spaces) and line breaks to improve readability. * Add comments to explain complex logic, API calls, and data transformations. * Use meaningful names for variables, functions, and parameters. **Don't Do This:** * Write long, monolithic functions without comments or clear structure. * Use cryptic or ambiguous names. **Why:** Consistent formatting and clear comments make the code easier to understand and maintain. ### 3.2. Standard: Transaction Management **Do This:** * Wrap API calls within explicit transactions when necessary to ensure data consistency. Use "BEGIN", "COMMIT", and "ROLLBACK". * Handle potential errors during API calls gracefully and roll back the transaction if necessary. **Don't Do This:** * Leave transactions open for extended periods of time while waiting for API responses. * Commit transactions before ensuring the success of all related API calls. **Why:** Proper transaction management ensures data integrity and prevents inconsistencies. ### 3.3. Standard: Testing **Do This:** * Write unit tests for API interaction functions to verify that they handle different scenarios correctly (e.g., success, error, timeout). * Use mock APIs or stubs to isolate the database from external dependencies during testing. * Write integration tests to ensure that the database and APIs work together seamlessly. **Don't Do This:** * Skip testing API interaction code. This can lead to unexpected errors and integration issues in production. * Rely solely on manual testing. **Why:** Automated testing improves code quality, reduces the risk of regressions, and facilitates continuous integration and delivery. These API integration standards will help create reliable, secure, and maintainable PostgreSQL applications that integrate effectively with external services. Remember to stay updated with the latest PostgreSQL features and best practices as the ecosystem evolves.