Re: Operator script error
| От | Bartosz Dmytrak |
|---|---|
| Тема | Re: Operator script error |
| Дата | |
| Msg-id | CAD8_Uca3342=9qXVtWivGHtkpGRdE9wbxksu=NtFpirOGhTZ9w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Operator script error (Guillaume Lelarge <guillaume@lelarge.info>) |
| Ответы |
Re: Operator script error
(Guillaume Lelarge <guillaume@lelarge.info>)
|
| Список | pgadmin-support |
Hi my script:
Regards,
Bartek
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"}'')
';
Bartek
2012/3/12 Guillaume Lelarge <guillaume@lelarge.info>
Can you give me the script that allowed you to create the operator? TheOn 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';
>
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
В списке pgadmin-support по дате отправления: