Page 1 of 1
Running a query that uses pg_trgm EXTENSION
Posted: Tue 03 Apr 2012 15:41
by 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
Posted: Fri 06 Apr 2012 11:45
by 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.
JORGEMAL wrote: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).
Posted: Fri 06 Apr 2012 21:38
by 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
Posted: Mon 09 Apr 2012 14:03
by Pinturiccio
JORGEMAL wrote: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.