Support executing an anonymous code block via DO

[yehudamakarov]

Version

1.13.0

What happened?

create table if not exists ebay_search_result
(
    id                  int           not null primary key generated always as identity,
    title               varchar(2000) not null,
    epid                varchar(100)  not null,
    link                varchar(2000) not null,
    image               varchar(2000) not null,
    hotness             varchar(2000) not null,
    condition           varchar(2000) not null,
    is_auction          bool          not null,
    buy_it_now          bool          not null,
    shipping_cost       real          not null,
    sponsored           bool          not null,
    best_offer_accepted bool          not null,
    price_raw           varchar(100)  not null,
    price_value         real          not null,
    price_currency      varchar(100)  not null,
    ended               date          not null,
    dup                 bool          not null default false,
    unique (epid)
)

above is the table in script 1.

then when adding a column, I cannot use the commented out syntax, or else sqlc doesn't understand the columns have been added even though they have been later in script 2:

-- DO $$
--     BEGIN
ALTER TABLE ebay_search_result
    ADD COLUMN marked_for_processing bool,
    ADD COLUMN is_processed          bool;
--     EXCEPTION
--         WHEN duplicate_column THEN
--             RAISE NOTICE 'Field already exists. Ignoring...';
--     END$$;

While commented out, everything works.

docker run -v $(pwd):/srv -w /srv kjconroy/sqlc:1.13.0 generate
# package sqlpullsales
jobapi/pullsales/queries/get_marked_for_processing.sql:19:8: column "marked_for_processing" does not exist
make: *** [generate] Error 1

the query get_marked_for_processing is:

-- name: GetMarkedForProcessing :many
select id,
       title,
       epid,
       link,
       image,
       hotness,
       condition,
       is_auction,
       buy_it_now,
       shipping_cost,
       sponsored,
       best_offer_accepted,
       price_raw,
       price_value,
       price_currency,
       ended,
       dup,
       marked_for_processing,
       is_processed
from ebay_search_result
where is_processed = false
  and marked_for_processing = true;

The above also works if I write the query with a *, but that is because it doesn't know about or see the new columns.

Relevant log output

No response

Database schema

No response

SQL queries

No response

Configuration

No response

Playground URL

No response

What operating system are you using?

No response

What database engines are you using?

No response

What type of code are you generating?

No response

[kyleconroy]

One of my favorite things about maintaining this project is all of the new SQL features I get to learn to about. I didn't know about DO. I switched this to a feature request because sqlc has currently does no support executing SQL functions.

[yehudamakarov]

epic! If you want to leave a note on how to accomplish I can take a look.

Thanks, Yehuda Makarov On May 12, 2022, 11:22 -0400, Kyle Conroy @.***>, wrote:

One of my favorite things about maintaining this project is all of the new SQL features I get to learn to about. I didn't know about DO. I switched this to a feature request because sqlc has currently does no support executing SQL functions. — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

[yehudamakarov]

Legendary. Thanks for the consistent hard work on this repo. <3