// afterConnect creates the prepared statements that this application uses
func afterConnect(conn *pgx.Conn) (err error) {
_, err = conn.Prepare("getUrl", `
select url from shortened_urls where id=$1
`)
if err != nil {
return
}
_, err = conn.Prepare("deleteUrl", `
delete from shortened_urls where id=$1
`)
if err != nil {
return
}
// There technically is a small race condition in doing an upsert with a CTE
// where one of two simultaneous requests to the shortened URL would fail
// with a unique index violation. As the point of this demo is pgx usage and
// not how to perfectly upsert in PostgreSQL it is deemed acceptable.
_, err = conn.Prepare("putUrl", `
with upsert as (
update shortened_urls
set url=$2
where id=$1
returning *
)
insert into shortened_urls(id, url)
select $1, $2 where not exists(select 1 from upsert)
`)
return
}
