function similarity(text, unknown) does not exist

[tungbhrelipa]

Describe the bug I created a simple query code

/* @name findSimilarContract */
SELECT *
FROM internal_transactions
WHERE
    created_contract_code <> ''
    AND created_contract_code IS NOT NULL
    AND SIMILARITY(ENCODE(created_contract_code, 'escape')::TEXT , :input_deployed_bytecode) >= :threshold
ORDER BY block_number ASC
LIMIT 1;

and it threw an error

Error in query. Details: {
  errorCode: 'ParseFuncOrColumn',
  hint: 'No function matches the given name and argument types. You might need to add explicit type casts.',
  message: 'function similarity(text, unknown) does not exist',
  position: '116'
}

I have found the way to use the similar function is that I have to run CREATE EXTENSION IF NOT EXISTS pg_trgm; first. Then, I updated my SQL code

/* @name findSimilarContract */
CREATE EXTENSION pg_trgm;
SELECT *
FROM internal_transactions
WHERE
    created_contract_code <> ''
    AND created_contract_code IS NOT NULL
    AND SIMILARITY(ENCODE(created_contract_code, 'escape')::TEXT , :input_deployed_bytecode) >= :threshold
ORDER BY block_number ASC
LIMIT 1;

And again, it threw another error

277 | /* @name findSimilarContract */
278 | CREATE EXTENSION pg_trgm;
279 > SELECT *
280 | FROM internal_transactions
281 | WHERE
282 |   created_contract_code <> ''
283 |   AND created_contract_code IS NOT NULL
284 |   AND SIMILARITY(ENCODE(created_contract_code, 'escape')::TEXT , :input_deployed_bytecode) >= :threshold
285 | ORDER BY block_number ASC
286 | LIMIT 1;
Errors:
- (279:0) Parse error: extraneous input 'SELECT' expecting {<EOF>, '/*'}

How can I fix this?

[tungbhrelipa]

I fixed this by read the doc and the solution code below:

import { sql } from "@pgtyped/runtime";
import { IFindByTransactionHashListResult } from "./internal_transactions.queries";

interface createSimilarityFunctionQuery {
  params: void;
  result: void;
}

export const createSimilarityFunction = sql<createSimilarityFunctionQuery>`
  CREATE EXTENSION IF NOT EXISTS pg_trgm;
`;

export interface IGetSimilarContractParams {
  input_deployed_bytecode: string;
  threshold: number;
}

export type IGetSimilarContractResult = IFindByTransactionHashListResult;

export interface IGetSimilarContractQuery {
  params: IGetSimilarContractParams;
  result: IGetSimilarContractResult;
}
export const findSimilarContract = sql<IGetSimilarContractQuery>`
SELECT *
FROM internal_transactions
WHERE
    created_contract_code <> ''
    AND created_contract_code IS NOT NULL
    AND SIMILARITY(ENCODE(created_contract_code, 'escape')::TEXT , $input_deployed_bytecode) >= $threshold
ORDER BY block_number ASC
LIMIT 1;
`;