#Help generating go code with sqlc

55 messages · Page 1 of 1 (latest)

night wind
# 
CREATE OR REPLACE FUNCTION find_product(
    _id UUID,
    _gtin VARCHAR(13),
    _store_id UUID,
    _name VARCHAR(255),
    _weight VARCHAR(255),
    _price INTEGER,
    _brand VARCHAR(255),
    _category VARCHAR(255)
)
RETURNS TABLE (
    id UUID,
    store_id UUID,
    name VARCHAR(255),
    price INTEGER,
    weight VARCHAR(255),
    expiration_date DATE,
    category VARCHAR(255),
    image_url VARCHAR(255),
    brand VARCHAR(255),
    gtin VARCHAR(13)
) AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT id, store_id, name, price, weight, expiration_date, category, image_url, brand, gtin
         FROM products
         WHERE (%L IS NULL OR id = %L) AND
               (%L IS NULL OR gtin = %L) AND
               (%L IS NULL OR store_id = %L) AND
               (%L IS NULL OR name = %L) AND
               (%L IS NULL OR weight = %L) AND
               (%L IS NULL OR price = %L) AND
               (%L IS NULL OR brand = %L) AND
               (%L IS NULL OR category = %L)
         LIMIT 1',
        _id, _id,
        _gtin, _gtin,
        _store_id, _store_id,
        _name, _name,
        _weight, _weight,
        _price, _price,
        _brand, _brand,
        _category, _category
    );
END;
$$ LANGUAGE plpgsql;

The params and the return type are being created empty in go

type SelectProductParams struct {
FindProduct interface{}
FindProduct_2 interface{}
FindProduct_3 interface{}
FindProduct_4 interface{}
FindProduct_5 interface{}
FindProduct_6 interface{}
FindProduct_7 interface{}
FindProduct_8 interface{}
}

type SelectProductRow struct {
}

keen cloud
#

is it not possible to just write the query? as opposed to doing dynamic string formatting

night wind
#

I wrote the query manually but I'm still getting 0 rows on the result

#

It's seems to be a little advanced to me

keen cloud
#

by "wrote the query manually" do you mean sql SELECT id, store_id, name, price, weight, expiration_date, category, image_url, brand, gtin FROM products WHERE (_id IS NULL OR id = _id) AND (_gtin IS NULL OR gtin = _gtin) AND (_store_id IS NULL OR store_id = _store_id) AND (_name IS NULL OR name = _name) AND (_weight IS NULL OR weight = _weight) AND (_price IS NULL OR price = _price) AND (_brand IS NULL OR brand = _brand) AND (_category IS NULL OR category = _category) LIMIT 1?

night wind
#

it's possible, yest

# 
CREATE OR REPLACE FUNCTION find_product(
  _id UUID,
  _gtin VARCHAR(13),
  _store_id UUID,
  _name VARCHAR(255),
  _weight VARCHAR(255),
  _price INTEGER,
  _brand VARCHAR(255),
  _category VARCHAR(255)
)
RETURNS TABLE (
  id UUID,
  store_id UUID,
  name VARCHAR(255),
  price INTEGER,
  weight VARCHAR(255),
  expiration_date DATE,
  category VARCHAR(255),
  image_url VARCHAR(255),
  brand VARCHAR(255),
  gtin VARCHAR(13)
) AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT id, store_id, name, price, weight, expiration_date, category, image_url, brand, gtin
      FROM products
      WHERE (%L IS NULL OR id = %L) AND
            (%L IS NULL OR gtin = %L) AND
            (%L IS NULL OR store_id = %L) AND
            (%L IS NULL OR name = %L) AND
            (%L IS NULL OR weight = %L) AND
            (%L IS NULL OR price = %L) AND
            (%L IS NULL OR brand = %L) AND
            (%L IS NULL OR category = %L)
      LIMIT 1',
    _id, _id,
    _gtin, _gtin,
    _store_id, _store_id,
    _name, _name,
    _weight, _weight,
    _price, _price,
    _brand, _brand,
    _category, _category
  );
END;
$$ LANGUAGE plpgsql;

-- name: SelectProduct :one
SELECT * FROM find_product(
  $1, $2, $3, $4, $5, $6, $7, $8
) LIMIT 1;
#

let me try

keen cloud
# night wind let me try

I'm not suggesting you try anything, at least not yet.
I don't use postgres, I'm trying to understand why you are doing string templating

#

because it seems to me that is a very easy way to make sqlc not understand what you are doing, given you are constructing a sql statement dynamically

#

so, I am asking, why are you doing that?

#

I have no frame of reference here

night wind
#

Lemme give you some context

goal: to create a dynamic select

table: products [id, name, weight, price...]

example:
input: name
sql: select * from products where name = $1

input: name and weight
sql: select * from products where name = $1 and weight = $2

input: price
sql: select * from products where price = $1

coral vine
#

Esse seria o natural esperado

#

Já usei o sqlc, mas nunca coloquei sql functions

#

Sempre definia queries e schemas

#

(I assumed you can speak Portuguese, due to the "Br" in your nickname, if I'm wrong, my apoligies

night wind
#

Brazil mentioned

#

Poderia me dar um exemplo??

meu caso de uso é o seguinte

Eu preciso verificar se um produto existe
Algumas vezes eu tenho nome e preço, outras nome e peso, outras nome, peso e marca

coral vine
#

(Perdão a menção, esqueci de desmarcar

night wind
#

isso, era a ideia

#

Seria o ideial kkk

#

mas acho que não vai dar pra fazer por agora, vou ter que me contentar com um (name AND weight AND price)

#

já que o produto pertence a uma loja específica

#

e esses 3 parâmetros eu consigo obrigar a existir

coral vine
#

Cara, tu tem controle sobre a table ou nem?

night wind
#

é um sistema novo que to criando, 100% meu

coral vine
#

Aah, olha

night wind
#

to fazendo a leitura de encartes usando AI

coral vine
#

Eu tenho uma recomendação, que embora possa parecer "gambiarra"

#

É o que MUITAS empresas com motor de busca usam

#

Twitter, instagram, no geral

#

Pra ter resultados mais "assertivos" sem uma busca avançada

night wind
#

sim sim, mas o objetivo não seria um elastic search da vida

#

esse caso de uso é pra inserir no db

#

na hora de retornar pro usuário eu vou usar um agolia da vida

#

o projeto é open source

coral vine
#

MEU AMIGO

#

646 arquivos

#

O projeto é muito interessante

#

Btw, a menos que tu ache viável sei lá, fazer na hora de inserir, um campo que seja um append dos outros campos, para depois fazer a busca por ele

#

Se pa vai ter de usar o AND mesmo

#

Sobre o sqlc, calmai

night wind
#

Nossa mano, consegui

night wind
#

vlw

#

vale muito a pena entender isso aqui, muito da hora

#

tu ta no trampo ai? acho que vou mostrar la na live

coral vine
#

To pilhado de coisa no momento, mas de toda forma

#

Mandei solicitacao de amizade, tu me manda na DM link dps qlqr coisa

night wind
#

vou comitar lá no git tb, dai tu da uma olhada no código