Hi my script:
CREATE OR REPLACE FUNCTION "Calc"."getDataRepository"(a text[])
RETURNS numeric AS
$BODY$
DECLARE
/*
ARRAY STRUCTURE
Metric_Name, Date, Value_Type
Value_Type:
1. N - Numerator
2. D - Denominator
*/
vRetValue "DataRepository"."tblDataRepository"."NumeratorValue"%TYPE;
vMetricName "Dictionary"."tblDataMetrics"."MetricName"%TYPE;
vRowDate "DataRepository"."tblDataRepository"."RowDate"%TYPE;
vDataMetricId "Dictionary"."tblDataMetrics"."DataMetricId"%TYPE;
BEGIN
vMetricName := a[1];
vRowDate := a[2]::timestamp(0);
SELECT "DataMetricId" INTO vDataMetricId
FROM "Dictionary"."tblDataMetrics"
WHERE "MetricName" = vMetricName;
CASE
WHEN a[3] = 'N' THEN
SELECT "NumeratorValue" INTO vRetValue
FROM "DataRepository"."tblDataRepository"
WHERE "DataMetricId" = vDataMetricId AND "RowDate" = vRowDate;
WHEN a[3] = 'D' THEN
SELECT "DenominatorValue" INTO vRetValue
FROM "DataRepository"."tblDataRepository"
WHERE "DataMetricId" = vDataMetricId AND "RowDate" = vRowDate;
ELSE
vRetValue := NULL;
END CASE;
RETURN vRetValue;
END
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION "Calc"."getDataRepository"(text[])
OWNER TO postgres;
CREATE OPERATOR "Calc".@#(
PROCEDURE = "Calc"."getDataRepository",
RIGHTARG = _text);
COMMENT ON OPERATOR "Calc".@#(NONE, _text) IS '
Usage Example:
SET LOCAL search_path = "Calc";
SELECT @# (''{"MY_METRIC","2012-01-01","N"}'')
';
Regards,
Bartek
2012/3/12 Guillaume Lelarge <guilla...@lelarge.info>
> On Mon, 2012-03-12 at 14:51 +0100, Bartosz Dmytrak wrote:
> > Hi,
> > it looks like operator script is generated incorrectly:
> > eg.
> > -- Operator: Calc.@#(NONE, _text) -- Lack of quotation
> >
> > -- DROP OPERATOR Calc.@#(NONE, _text); -- Lack of quotation
> >
> > CREATE OPERATOR Calc.@#(
> > PROCEDURE = """Calc"".""doSomething""", -- Wrong quotation
> > RIGHTARG = _text);
> > COMMENT ON OPERATOR Calc.@#(NONE, _text) IS 'this is comment'; -- Lack
> of
> > quotation
> >
> >
> > Schema name and function should be quoted properly:
> >
> > -- Operator: "Calc".@#(NONE, _text)
> >
> > -- DROP OPERATOR "Calc".@#(NONE, _text);
> >
> > CREATE OPERATOR "Calc".@#(
> > PROCEDURE ="Calc"."doSomething",
> > RIGHTARG = _text);
> > COMMENT ON OPERATOR "Calc".@#(NONE, _text) IS 'this is comment';
> >
>
> Can you give me the script that allowed you to create the operator? The
> script should contain the function definition. Without it, it would be a
> lot of work to reproduce your issue.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>
