[SQL] Tsearch2 headline usage
Hi There,
We are using tsearch2 for FTS implementation. For highlighting the search term
in the result we are displaying the output of headline function which is
supposed to tag (mark up) those stemmed words in the text that match any of the
stemmed words in search term. The problem is that some initial part of the text
is being truncated from the headline output. Can anyone please suggest on how
to overcome this phenomenon.
tradein_clients=> SELECT headline( 'A
Leading manufacturer & Exporter of TAJ Brand Ice cream Plant &
Dairy Equipments with complete plants in various capacities.
Pasteuriser, Homogenizer, Surface Cooler, Plate Chiller, Ageing Vat,
Ice Cream Continuous Freezer, Single Flavour Softy, Bulk Milk Coolers.'
, to_tsquery('ice&cream&plant') , 'StartSel=,
StopSel=' ) ;
headline
cream Plant & Dairy
Equipments with complete plants in various
capacities. Pasteuriser, Homogenizer, Surface Cooler, Plate Chiller,
Ageing Vat, Ice Cream
(1 row)
Regds
Mallah.
Re: [SQL] Multiple SRF parameters from query
On Wed, 2005-06-01 at 10:29 +0530, Ramakrishnan Muralidharan wrote:
> Hi,
>
> I am not able to understand "Returning only one row", since 'aaa' having 2
> rows and 'bbb' having 3 rows and what criteria single row should be returned.
> Please let me know the expected result and I will try to find out a solution
> for it.
Sorry, it was not clear from my message.
I meant that I had success creating a table as a result of multiple
invocation of foo (parameter) using the result of an "outer" query as
list of parameter to foo, only if foo () is a "normal" function,
returning a simple value.
It was a misleading sentence anyway, since what i'm asking help for is
the case where foo () is a SRF and returns more than 1 row.
What i'm trying to do is a query where i get a result of this kind
a | b | c
--+---+---
1 | 2 | 3
5 | 9 | 1
4 | 0 | 0
2 | 0 | 0
0 | 0 | 0
given that i select from the table Anagrafica the fields 'aaa' and
'bbb', where the first 2 rows obtained as the result of foo ('aaa') and
the latter 3 as the result of foo ('bbb').
Thanks a lot for help !
PS: Sorry for late answering, i had problems with mail.
Regards,
Federico.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Joe Conway wrote:
> Tom Lane wrote:
> > I think he's got a good point, actually. We document the ARRAY-with-
> > parens-around-a-SELECT syntax as
> >
> > The resulting one-dimensional array will have an element for
> > each row in the subquery result, with an element type matching
> > that of the subquery's output column.
> >
> > To me, that implies that a subquery result of no rows generates a
> > one-dimensional array of no elements, not a null array.
>
> OK, looks like I'm outnumbered.
>
> But as far as I know, we have never had a way to produce a
> one-dimensional empty array. Empty arrays thus far have been dimensionless.
>
> Assuming we really want an empty 1D array, I created the attached patch.
> This works fine, but now leaves a few oddities to be dealt with, e.g.:
>
> regression=# select array_dims(array(select 1 where false));
> array_dims
>
> [1:0]
> (1 row)
>
> Any thoughts on how this should be handled for an empty 1D array?
>
> > The point Markus is complaining about seems like it should
> > be easily fixable.
>
> Well, "easily" is a relative term. My Postgres hacking neurons have
> gotten kind of rusty lately -- but then maybe that was your underlying
> point ;-)
No one responed to this email, so I will try. Is this the one
dimmentional array you were talking about?
test=> select array_dims('{}'::integer[]);
array_dims
(1 row)
Why is [1:0] wrong to return?
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Bruce Momjian wrote:
Joe Conway wrote:
Any thoughts on how this should be handled for an empty 1D array?
No one responed to this email, so I will try. Is this the one
dimmentional array you were talking about?
test=> select array_dims('{}'::integer[]);
array_dims
(1 row)
In this case, what you get is actually a dimensionless array. Literally,
you get this:
if (nitems == 0)
{
/* Return empty array */
retval = (ArrayType *) palloc0(sizeof(ArrayType));
retval->size = sizeof(ArrayType);
retval->elemtype = element_type;
PG_RETURN_ARRAYTYPE_P(retval);
}
I.e. the array structure is allocated, the size is set (which is
required since arrays are varlena), and the element type is initialized.
There is no initialization of ndim, ARR_DIMS(), or ARR_LBOUND().
In this case, since there are no dimensions, array_dims() probably does
the right thing by returning NULL.
Why is [1:0] wrong to return?
I'm not sure it is wrong -- it just seems a bit strange. The difference
is that in order to return an empty *one-dimensional* array, ndim,
ARR_DIMS(), and ARR_LBOUND() are all appropriately set (by the patched
code). Basically, ndim == 1, ARR_DIMS() is a single element int array (C
array that is) indicating 0 elements for dimension 1, and ARR_LBOUND()
is a single element int array indicating a lower bound of 1. This leads
to the array_dims() return value of [1:0]. The value 1 is unquestionably
correct for the lower bound index, but what should be reported for the
upper bound? We can't return [1:1], because that would indicate that we
have one element.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Joe Conway wrote:
> Bruce Momjian wrote:
> > Joe Conway wrote:
> >>
> >>Any thoughts on how this should be handled for an empty 1D array?
> >
> > No one responed to this email, so I will try. Is this the one
> > dimmentional array you were talking about?
> >
> > test=> select array_dims('{}'::integer[]);
> > array_dims
> >
> >
> > (1 row)
>
> In this case, what you get is actually a dimensionless array. Literally,
> you get this:
>
> if (nitems == 0)
> {
> /* Return empty array */
> retval = (ArrayType *) palloc0(sizeof(ArrayType));
> retval->size = sizeof(ArrayType);
> retval->elemtype = element_type;
> PG_RETURN_ARRAYTYPE_P(retval);
> }
>
> I.e. the array structure is allocated, the size is set (which is
> required since arrays are varlena), and the element type is initialized.
> There is no initialization of ndim, ARR_DIMS(), or ARR_LBOUND().
>
> In this case, since there are no dimensions, array_dims() probably does
> the right thing by returning NULL.
>
> > Why is [1:0] wrong to return?
> >
>
> I'm not sure it is wrong -- it just seems a bit strange. The difference
> is that in order to return an empty *one-dimensional* array, ndim,
> ARR_DIMS(), and ARR_LBOUND() are all appropriately set (by the patched
> code). Basically, ndim == 1, ARR_DIMS() is a single element int array (C
> array that is) indicating 0 elements for dimension 1, and ARR_LBOUND()
> is a single element int array indicating a lower bound of 1. This leads
> to the array_dims() return value of [1:0]. The value 1 is unquestionably
> correct for the lower bound index, but what should be reported for the
> upper bound? We can't return [1:1], because that would indicate that we
> have one element.
OK, so '[1:0]' seems correct. How would to specify such an array
manually in a string?
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Multiple SRF parameters from query
On Sat, Jun 04, 2005 at 06:19:35PM +0200, Federico Pedemonte wrote:
>
> What i'm trying to do is a query where i get a result of this kind
>
> a | b | c
> --+---+---
> 1 | 2 | 3
> 5 | 9 | 1
> 4 | 0 | 0
> 2 | 0 | 0
> 0 | 0 | 0
>
> given that i select from the table Anagrafica the fields 'aaa' and
> 'bbb', where the first 2 rows obtained as the result of foo ('aaa') and
> the latter 3 as the result of foo ('bbb').
If you don't mind using a deprecated feature that might be removed
from future versions of PostgreSQL, then see "SQL Functions Returning
Sets" in the documentation:
http://www.postgresql.org/docs/8.0/static/xfunc-sql.html#AEN29555
To use the deprecated feature, you could wrap a complex PL/pgSQL
SRF inside a simple SQL SRF. The following example works for me
in 8.0.3:
CREATE TABLE anagrafica (
id text PRIMARY KEY,
n integer NOT NULL
);
INSERT INTO anagrafica (id, n) VALUES ('aaa', 1);
INSERT INTO anagrafica (id, n) VALUES ('bbb', 5);
INSERT INTO anagrafica (id, n) VALUES ('ccc', 9);
INSERT INTO anagrafica (id, n) VALUES ('ddd', 10);
INSERT INTO anagrafica (id, n) VALUES ('eee', 11);
CREATE TYPE footype AS (
a integer,
b integer,
c integer
);
CREATE FUNCTION foo(id text) RETURNS SETOF footype AS $$
DECLARE
rec footype;
BEGIN
IF id = 'aaa' THEN
rec.a := 1; rec.b := 2; rec.c := 3; RETURN NEXT rec;
rec.a := 5; rec.b := 9; rec.c := 1; RETURN NEXT rec;
RETURN;
ELSIF id = 'bbb' THEN
rec.a := 4; rec.b := 0; rec.c := 0; RETURN NEXT rec;
rec.a := 2; rec.b := 0; rec.c := 0; RETURN NEXT rec;
rec.a := 0; rec.b := 0; rec.c := 0; RETURN NEXT rec;
RETURN;
ELSE
rec.a := -1; rec.b := -2; rec.c := -3; RETURN NEXT rec;
RETURN;
END IF;
END;
$$ LANGUAGE plpgsql STABLE STRICT;
CREATE FUNCTION bar(text) RETURNS SETOF footype AS $$
SELECT * FROM foo($1);
$$ LANGUAGE sql STABLE STRICT;
SELECT (bar(id)).* FROM anagrafica WHERE n <= 5;
a | b | c
---+---+---
1 | 2 | 3
5 | 9 | 1
4 | 0 | 0
2 | 0 | 0
0 | 0 | 0
(5 rows)
I don't know if there's a way to do this in a simple query without
relying on the deprecated behavior. For forward compatibility, you
might be better off writing a SRF that makes a query and loops
through the results, like this:
CREATE FUNCTION baz(query text) RETURNS SETOF footype AS $$
DECLARE
rec record;
retval footype;
BEGIN
FOR rec IN EXECUTE query LOOP
IF rec.id = 'aaa' THEN
retval.a := 1; retval.b := 2; retval.c := 3; RETURN NEXT retval;
retval.a := 5; retval.b := 9; retval.c := 1; RETURN NEXT retval;
ELSIF rec.id = 'bbb' THEN
retval.a := 4; retval.b := 0; retval.c := 0; RETURN NEXT retval;
retval.a := 2; retval.b := 0; retval.c := 0; RETURN NEXT retval;
retval.a := 0; retval.b := 0; retval.c := 0; RETURN NEXT retval;
ELSE
retval.a := -1; retval.b := -2; retval.c := -3; RETURN NEXT retval;
END IF;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql STABLE STRICT;
SELECT * FROM baz('SELECT * FROM anagrafica WHERE n <= 5');
a | b | c
---+---+---
1 | 2 | 3
5 | 9 | 1
4 | 0 | 0
2 | 0 | 0
0 | 0 | 0
(5 rows)
Maybe somebody else can suggest improvements or alternatives.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
