Running a query that uses pg_trgm EXTENSION

[JORGEMAL]

I am trying to run a query that uses the "pg_trgm EXTENSION" as follows:

Code: Select all

strQuery.Append("SELECT ");
strQuery.Append("nombre, ");
strQuery.Append("similarity(nombre, '" + strArtista + "') AS similitud ");
strQuery.Append("FROM temp_artistas ");
strQuery.Append("WHERE ");
strQuery.Append("similarity(nombre, '" + strArtista + "')  1 ");
strQuery.Append("AND similarity(nombre, '" + strArtista + "') >= 0.5");

where "nombre" is a field of a table and "strArtista" is any string of characters. Neverthleless, I get a message saying that the "function similarity does not exist". Such a query runs fine in Visual Studio and in PostgreSQL Maestro. What am I missing?

PS: The extension is already created. I use dotConnect v5.70 and PostgreSQL v9.1.

Regards,
Jorge Maldonado

[Pinturiccio]

We could not reproduce the issue. After installing pg_trgm and creating pg_trgm EXTENSION we can use it in pgAdmin and dotConnect for PostgreSQL.

Such a query runs fine in Visual Studio and in PostgreSQL Maestro

How do you run such a query in Visual Studio?
Please make sure that you connect to the same database in PostgreSQL Maestro and in your application.

If you install pg_trgm and create pg_trgm EXTENSION and if trgm functions can be run in pgAdmin, then they can be run with dotConnect for PostgreSQL (only for the same database).

[JORGEMAL]

Did you write C# and tested the SIMILARITY function?
If so, would you show me your code so I can go over it and see what I am doing wrong? I have been testing for a long time and I keep getting the message that says that the "functionality(character varying, unknown) function does not exist".

I made a mistake when I say that I tested in Visual Studio, I mean pgAdmin III (and a tool called PostgreSQL Maestro too). Nevertheless, I just tested in the Visual Studio SQL Panel and it failed, I get the same error message.

I thank you in advace for any advice.

P.S.: I tried to send a zip file with a test Visual Studio web form using the contact form but I keep getting a "Failure sending mail" message".

With respect,
Jorge Maldonado

[Pinturiccio]

Did you write C# and tested the SIMILARITY function?
If so, would you show me your code so I can go over it and see what I am doing wrong?

Yes, i did. I'm posting my sample here:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("host=localhost;port=5434;user id=postgres;password=postgres;");
conn.Open();
PgSqlCommand comm = new PgSqlCommand("select dname, similarity(dname, 'Hillo') from dept where similarity(dname, 'Hillo')  1 AND similarity(dname, 'Hillo') >= 0", conn);
PgSqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
    for (int i = 0; i < reader.FieldCount; i++)
        Console.Write(reader.GetValue(i) + "\t");
    Console.WriteLine();
}

I have received the same error as you, before installing pg_trgm and creating pg_trgm EXTENSION. Now use the pg_trgm functions in pgAdmin III and in C# code with dotCOnnect for PostgreSQL.
Please make sure that you can run queries with pg_trgm functions in pgAdmin III. Then you can use these funtions with dotConnect for PostgreSQL but only with the same database.