go-micro.dev/v5@v5.12.0/store/postgres/pgx/templates.go (about)

     1  package pgx
     2  
     3  // init
     4  
     5  const createSchema = "CREATE SCHEMA IF NOT EXISTS %s"
     6  const createTable = `CREATE TABLE IF NOT EXISTS %s.%s
     7  (
     8  	key text primary key,
     9  	value bytea,
    10  	metadata JSONB,
    11  	expiry timestamp with time zone
    12  )`
    13  const createMDIndex = `create index if not exists idx_md_%s ON %s.%s USING GIN (metadata)`
    14  const createExpiryIndex = `create index if not exists idx_expiry_%s on %s.%s (expiry) where (expiry IS NOT NULL)`
    15  
    16  // base queries
    17  const (
    18  	list     = "SELECT key FROM %s.%s WHERE key LIKE $1 and (expiry < now() or expiry isnull)"
    19  	readOne  = "SELECT key, value, metadata, expiry FROM %s.%s WHERE key = $1 and (expiry < now() or expiry isnull)"
    20  	readMany = "SELECT key, value, metadata, expiry FROM %s.%s WHERE key LIKE $1 and (expiry < now() or expiry isnull)"
    21  	write    = `INSERT INTO %s.%s(key, value, metadata, expiry)
    22  VALUES ($1, $2::bytea, $3, $4)
    23  ON CONFLICT (key)
    24  DO UPDATE
    25  SET value = EXCLUDED.value, metadata = EXCLUDED.metadata, expiry = EXCLUDED.expiry`
    26  	deleteRecord  = "DELETE FROM %s.%s WHERE key = $1"
    27  	deleteExpired = "DELETE FROM %s.%s WHERE expiry < now()"
    28  )
    29  
    30  // suffixes
    31  const (
    32  	limit = " LIMIT $2 OFFSET $3"
    33  	asc   = " ORDER BY key ASC"
    34  	desc  = " ORDER BY key DESC"
    35  )