PROXY

Also known as

reverse_proxy (with or without @ prefix)

Mark endpoint as a reverse proxy that forwards requests to an upstream service.

Syntax

@proxy
@proxy [ host_url ]
@proxy [ http_method ]
@proxy [ http_method ] [ host_url ]

Description

The proxy annotation marks an endpoint as a reverse proxy. When a request arrives, NpgsqlRest forwards it to an upstream service and either returns the response directly (passthrough mode) or passes it to your PostgreSQL function for processing (transform mode).

Basic Usage

Passthrough Mode

For simple proxy forwarding without database processing:

create function get_external_data()
returns void
language sql as 'select';

comment on function get_external_data() is 'HTTP GET
@proxy';

When the function has no proxy response parameters, the upstream response is returned directly to the client without opening a database connection.

Transform Mode

To process the upstream response in PostgreSQL:

create function get_and_transform(
    _proxy_status_code int default null,
    _proxy_body text default null,
    _proxy_headers json default null,
    _proxy_content_type text default null,
    _proxy_success boolean default null,
    _proxy_error_message text default null
)
returns json
language plpgsql as $$
begin
    if not _proxy_success then
        return json_build_object('error', _proxy_error_message);
    end if;
    return json_build_object(
        'status', _proxy_status_code,
        'data', _proxy_body::json
    );
end;
$$;

comment on function get_and_transform(int, text, json, text, boolean, text) is 'HTTP GET
@proxy';

Proxy Annotations

Basic Proxy with Default Host

Uses the host from ProxyOptions.Host configuration:

comment on function my_func() is '@proxy';

Proxy with Custom Host

Override the default host:

comment on function my_func() is '@proxy https://api.example.com';

Proxy with Custom HTTP Method

Override the upstream HTTP method (uses default host from configuration):

comment on function my_func() is '@proxy POST';

Combined Method and Host

Specify both HTTP method and host:

comment on function my_func() is '@proxy POST https://api.example.com';

Response Parameters

When the PostgreSQL function has parameters matching these names (configurable in ProxyOptions), the proxy response data is passed to the function:

Parameter Name	Type	Description
_proxy_status_code	int	HTTP status code from upstream (e.g., 200, 404).
_proxy_body	text	Response body content.
_proxy_headers	json	Response headers as JSON object.
_proxy_content_type	text	Content-Type header value.
_proxy_success	boolean	True for 2xx status codes.
_proxy_error_message	text	Error message if request failed.

Examples

API Gateway Pattern

Forward requests to different microservices:

-- Users service
create function users_api()
returns void
language sql as 'select';

comment on function users_api() is 'HTTP GET /api/users
@proxy https://users-service.internal:8080';

-- Orders service
create function orders_api()
returns void
language sql as 'select';

comment on function orders_api() is 'HTTP GET /api/orders
@proxy https://orders-service.internal:8080';

Data Enrichment

Fetch external data and enrich it with local data:

create function get_enriched_weather(
    city text,
    _proxy_status_code int default null,
    _proxy_body text default null,
    _proxy_success boolean default null
)
returns json
language plpgsql as $$
declare
    local_data json;
begin
    -- Get local city preferences
    select json_build_object('favorite', is_favorite, 'notes', notes)
    into local_data
    from user_city_preferences
    where city_name = city;

    if not _proxy_success then
        return json_build_object('error', 'Weather API unavailable');
    end if;

    return json_build_object(
        'weather', _proxy_body::json,
        'local', coalesce(local_data, '{}'::json)
    );
end;
$$;

comment on function get_enriched_weather(text, int, text, boolean) is 'HTTP GET
@proxy https://api.weather.com/v1/current?city={city}';

Authenticated Proxy with User Context

Forward authenticated requests with user information:

create function secure_api_call(
    _proxy_status_code int default null,
    _proxy_body text default null
)
returns json language plpgsql as $$
begin
    return json_build_object('status', _proxy_status_code, 'data', _proxy_body);
end;
$$;

comment on function secure_api_call(int, text) is 'HTTP GET
@authorize
@user_context
@proxy https://secure-api.internal/data';

User context values are forwarded as HTTP headers to the upstream service.

Proxy with User Parameters

Forward user claims as query parameters:

create function proxy_with_user(
    _user_id text default null,
    _proxy_body text default null
)
returns json language plpgsql as $$
begin
    return _proxy_body::json;
end;
$$;

comment on function proxy_with_user(text, text) is 'HTTP GET
@authorize
@user_params
@proxy https://api.internal/user-data';

The _user_id parameter value is forwarded to the upstream as ?userId=....

Configuration

Enable proxy functionality in your configuration:

{
  "NpgsqlRest": {
    "ProxyOptions": {
      "Enabled": true,
      "Host": "https://api.example.com",
      "DefaultTimeout": "30 seconds"
    }
  }
}

See Proxy Options for complete configuration reference.

Related

• Proxy Options - Proxy configuration reference
• user_context - Enable user context forwarding
• user_parameters - Enable user parameters forwarding
• authorize - Require authentication
• HTTP - Expose function as HTTP endpoint