Feature Request: Formatting functionality for SQL value literals / incorporate %L in postgres's format function

[apjoseph]

I found myself needing to do some SQL templating and discovered that duckdb doesn't have any obvious mechanism to format literals

format('CREATE TABLE test( id varchar GENERATED ALWAYS as %L )','1')

with postgres's format function would return

CREATE TABLE test( id varchar GENERATED ALWAYS as '1' )

It would be awesome to be able to do something like the following:

format('CREATE TABLE test( id varchar GENERATED ALWAYS as {:L} )','1')

I think the function that would need to be exposed is

Value::ToSQLString()

More broadly, support for templating via SQL is somewhat lacking. For instance there is no way to execute a multi-statement script generated from a macro via sql or to load a script from file within another sql file. As duckdb does not currently support multiple inserts and updates within a CTE, templating is necessary when you want to avoid having to write logic in the client. Since duckdb shines in the emdedded use case, keeping logic within SQL is more feasible than with server-side Postgres stored procedures which must potentially be reconciled with multiple codebase -so improved SQL templating functionality in DuckDB would be of great value.

[apjoseph]

wrong repo